Case Statements

[renee35]

Could someone please help me figure out what I am doing wrong in the code below:

Select
'Customer' = c.name,
'EmployeeID' = h.employeeid,
'First Name' = h.firstname,
'Last Name' = h.lastname,
'Job Description' = h.jobdesc,
'Regular Pay' = Case when r.paycodeid = 1 then t.payrate else 0 end,
'Regular Bill' = Case when r.paycodeid = 1 then t.billrate else 0 end,
'Overtime1 Pay' = Case when r.paycodeid = 2 then t.payrate else 0 end,
'Overtime1 Bill' = Case when r.paycodeid = 2 then t.billrate else 0 end,
'Overtime2 Pay' = Case when r.paycodeid = 3 then t.payrate else 0 end,
'Overtime2 Bill' = Case when r.paycodeid = 3 then t.billrate else 0 end,
'Overtime3 Pay' = Case when r.paycodeid = 4 then t.payrate else 0 end,
'Overtime3 Bill' = Case when r.paycodeid = 4 then t.billrate else 0 end,
'Overtime4 Pay' = Case when r.paycodeid = 5 then t.payrate else 0 end,
'Overtime4 Bill' = Case when r.paycodeid = 5 then t.billrate else 0 end
from
(
Select
e.employeeid, e.firstname, e.lastname, w.jobdesc, w.woid
from empemployee e
join woworkorder w on w.employeeid = e.employeeid

) h
join prcheckheader p on p.employeeid = h.employeeid
join prchecktc r on r.checkhistid = p.checkhistid and r.woid = h.woid
join vw_tcdetailall t on t.tcdetailid = r.tcdetailid
join custcustomer c on c.custid = r.custid
where r.paycodeid in (1,2,3,4,5) and (c.custid = @Custid or c.name like @CustomerName) and checkdate between @Startdate and @Enddate
Group by h.firstname, h.lastname, h.employeeid, c.name, h.jobdesc, r.paycodeid, t.payrate, t.billrate, p.checkdate




I want the fields to print out as such:

Regular Overtime1 Overtime2 Overtime3 Overtime4,

Instead they are printing out as:

Regular
Overtime1
Overtime2
Overtime3
Overtime4

Thanks!!

Thanks a bunch!!

-T

 

[bborissov]

Check thread183-1530163
Although the query is not the same the solution is
Just SUM() all pay types:

 SELECT ....
SUM(Case when r.paycodeid = 1 then t.payrate else 0 end) AS 'Regular Pay' ,
SUM(...)
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[renee35]

That didn't work (.. Thanks anyway!!

Thanks a bunch!!

-T

 

[bborissov]

Change GROUP BY to:

Group by c.name,
         h.employeeid,
         h.firstname,
         h.lastname,
         h.jobdesc

and try again.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.