The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Query to retrieve like this:

I am using SQL Server 2005.

I have a table with records like this:

Date              EmpID    ADCode      ADAmount
-------------------------------------------------
01-Jul-07        101      GPF            150
01-Jul-07        102      GPF.ADV        100
01-Jul-07        103      GPF            200
01-jul-07        104      GPF            300

I want to show results like this using a single SQL query:

Date      EmpID      GPF        GPF.ADV
-------------------------------------------------
01-Jul-07  101        150              0
01-Jul-07  102        0                100
01-Jul-07  103        200              0
01-Jul-07  104        300              0


I tried:

select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord

It is showing like this:

Date      EmpID      GPF        GPF.ADV
-----------------------------------------
01-Jul-07  101        0          0
01-Jul-07  101        150        0
01-Jul-07  102        0          100
01-Jul-07  103        0          0
01-Jul-07  103        1000        0
01-Jul-07  103        0          1000

It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
RK Send private email
Friday, August 24, 2007
 
 
Search for pivot queries.

This might help:


create table t (
  dt      date,
  empid    number,
  adcode  varchar(7),
  adamount number
);


insert into t values
(date '2007-07-01', 101, 'GPF'    , 150);
insert into t values
(date '2007-07-01', 102, 'GPF.ADV', 150);
insert into t
values (date '2007-07-01', 103, 'GPF'    , 150);
insert into t
values (date '2007-07-01', 104, 'GPF'    , 150);


select dt,
      empid,
      max(case adcode when 'GPF'   
          then adamount else null end) gpf,
      max(case adcode when 'GPF.ADV'
          then adamount else null end) gpf
  from t
 group by dt, empid;


René
René Nyffenegger
Friday, August 24, 2007
 
 
There are gaps in the question:
- table and query field names do not match, so it's unclear what is the data query works with;
- a select from the "a table with records" without WHERE, HAVING, JOIN, and GROUP BY clauses would always return the original 4 records, not 6.

It's a very good practice to have script like René put in, so someone could try it out in Query Analizer:

begin tran
-- step 1. create test (temporary) tables, other objects
-- step 2. populate test data
-- step 3. run the query
-- result. note difference b/w expected and actual results
rollback tran
DK Send private email
Friday, August 24, 2007
 
 
oops. make it "...without WHERE, GROUP BY, joins, and aggregate functions..."
DK Send private email
Friday, August 24, 2007
 
 
Rene,

It is not accepting group by clause.
RK Send private email
Friday, August 24, 2007
 
 
select PaySlipDate,EmpID,
SUM(case ADCode when 'GPF' then ADAmount else 0 end) GPF,
SUM(case ADCode when 'GPF.ADV' then ADAmount else 0 end) 'GPF.ADV'
from EmpSalaryRecord
GROUP BY PaySlipDate, EmpID
Cade Roux Send private email
Friday, August 24, 2007
 
 
This sounds like business/display logic pushed to the DB.  If possible, move it out.  If not,

+1 Cade Roux when its numerical data. 

for Text data, you can use a subqueries in the select, like this:

select PaySlipDate
        , EmpId
        , isnull(( select ADAmount from EmpSalaryRecord e1
            where e1.PaySlipDate = e.PaySlipDate
            and e1.EmpId = e.EmpId
            and e1.AdCode = 'GPF'), 0) as 'GPF'
        , isnull(( select ADAmount from EmpSalaryRecord e2
            where e2.PaySlipDate = e.PaySlipDate
            and e2.EmpId = e.EmpId
            and e2.AdCode = 'GPF.ADV'), 0) as 'GPF.ADV'
from EmpSalaryRecord e

Monday, August 27, 2007
 
 
+1 Cade Roux. This is the standard solution. See also keyword CROSSTAB. Ways of doing this in various SQL's are usually awkward because the original relational model just does not provide for it.
Mikkin
Monday, August 27, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz