Summing Multiple Case Statements

[titans4771]

In the code below, I am trying to sum a couple of my columns that are using case statements. The column at the bottom, PTEFee is the column where I am trying to sum everything up. Here is the code:

Select
'TotalMilieage' = SUM(case when y.paycodeid = 261 then isnull(y.units,0) else 0 end),
'MileageRate' = SUM(Case when y.paycodeid = 261 then isnull(y.billrate,0) else 0 end),
'ExpFee' = SUM(case when y.paycodeid = 259 and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid = 259 then ISNULL(y.billrate,0) end),
'TotalExpenseAmount' = SUM(case when y.paycodeid = 259 and y.computeonunits = 1 then (y.units*y.payrate)
when y.paycodeid = 259 then ISNULL(y.payrate,0) end),
'BonusFee' = SUM(case when y.paycodeid in (33,286,337,1959) and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid in (33,286,337,1959) then ISNULL(y.billrate,0) end),
'BonusAmount' = SUM(case when y.paycodeid in (33,286,337,1959) and computeonunits = 1 then (y.units*y.payrate)
when y.paycodeid in (33,286,337,1959) then ISNULL(y.payrate,0) end),
'IncentiveFee' = SUM(case when y.paycodeid in(260,264) and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid in(260,264) then ISNULL(y.billrate,0) end),
'TotalIncentiveAmount' = SUM(case when y.paycodeid in(260,264) and y.computeonunits = 1 then (y.units*y.payrate)
when y.paycodeid in(260,264) then y.payrate end),
'Regular Hours' = SUM(case when y.paycodeid = 1 then ISNULL(y.units,0) else 0 end),
'Overtime Hours' = SUM(case when y.paycodeid = 2 then ISNULL(y.units,0) else 0 end),
'TotalHours' = SUM(case when y.paycodeid = 1 then isnull(y.units,0) else 0 end)+SUM(case when y.paycodeid = 2 then isnull(y.units,0) else 0 end),
'PTEFee' = (SUM(case when y.paycodeid = 259 and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid = 259 then ISNULL(y.billrate,0) end)+
SUM(case when y.paycodeid in (33,286,337,1959) and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid in (33,286,337,1959) then ISNULL(y.billrate,0) end)+
SUM(case when y.paycodeid in(260,264) and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid in(260,264) then ISNULL(y.billrate,0) end))
from
emp e
inner join workorder w on w.employeeid = e.employeeid
inner join checkheader p on p.employeeid = e.employeeid
inner join
(
Select
checkhistid, r.units, r.woid, r.paycodeid, r.payrate, computeonunits, billrate, billunits
from checktc r
inner join tcdetail t on t.tcdetailid = r.tcdetailid
inner join tcheadera h on h.tcid = t.tcid
inner join cust c on c.custid = r.custid
inner join paycodes s on s.paycodeid = r.paycodeid
where startdate between '2008-2-1' and '2008-2-4' and
r.paycodeid in (1,2,259,260,264,33,286,337,1959) and
r.custid = 1 and employeeid = 39489
) y on y.checkhistid = p.checkhistid and y.woid = w.woid

Please let me know what I am doing wrong as to why the PTEFee column is not adding up correctly. It returns NULL in that field

Thanks!!

 

[gmmastros]

Anything added to NULL will always result in NULL.

Ex:

Select Null + 1

SUM will ignore null values.

Ex:

Declare @Temp Table(Data Int)

Insert into @Temp Values(1)
Insert into @Temp Values(2)
Insert into @Temp Values(3)
Insert into @Temp Values(NULL)

Select Sum(Data) From @Temp

But...... if all the values are NULL, SUM will return NULL

Ex:

Declare @Temp Table(Data Int)

Insert into @Temp Values(1)
Insert into @Temp Values(2)
Insert into @Temp Values(3)
Insert into @Temp Values(NULL)

Select Sum(Data) From @Temp Where Data > 10

Now, let's take a close look at your code.



[tt][blue]
case when y.paycodeid = 259 and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid = 259 then ISNULL(y.billrate,0) end
[/blue][/tt]

If you have a set of data without a paycodeid = 259, then this case statement will return null. To correct this, you could include an ELSE in to your case statement.

[tt][blue]
case when y.paycodeid = 259 and y.computeonunits = 1 then (y.billunits*y.billrate)
when y.paycodeid = 259 then ISNULL(y.billrate,0)
[!]Else 0 [/!]end
[/blue][/tt]

With this case statement, the only possible way to get a null value would be is y.billunits is null or y.billrate is null.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[titans4771]

Thanks, adding ELSE 0 did it.