CODEWITHSUNDEEP
t-sql
DJGray
DJGray

Reputation: 512

Trouble with sums and a having clause

I have a query that populates a report of "written off" hours, the total of which is the sum RegHrs, OvtHrs, and SpecialOvtHrs, but only those values with a positive value (each of these fields may have positive and negative values - positive values are "written off").

The query I am using (which doesn't work) is:

select
    LD.Employee,
    max(EM.LastName + ', ' + Em.FirstName) as EMName,
    LD.WBS1, LD.WBS2,
    sum(LD.RegHrs + LD.OvtHrs + LD.SpecialOvtHrs) as [Hours],
    CL.Name as ClientName,  pctf.CustProgram,
    max(PR.Name) as ProjName,
    LD.PKey, ISNULL(BillingDirectives.Comment, 'None') 
from AnvilProd..LD 
left join AnvilProd..PR on LD.WBS1 = PR.WBS1 and PR.WBS2 = ' ' and PR.WBS3 = ' '
left join AnvilProd..EM on LD.Employee = EM.Employee
left join AnvilProd..CL on PR.ClientID = CL.ClientID
left join AnvilProd..ProjectCustomTabFields pctf on PR.WBS1 = pctf.WBS1 and pctf.WBS2 = ' ' and pctf.WBS3 = ' '
left join InterfaceDev..BillingDirectives on BillingDirectives.PKey = LD.PKey   
where LD.BillStatus = 'X'
and LD.WrittenOffPeriod = @custPeriod
and LD.WBS1 not in (select distinct WBS1 from AnvilProd..BT where FeeBasis = 'L')
and LD.WBS1 not in (select distinct WBS1 from InterfaceDev..CircledHoursReportEliminatedJobs where ActiveStatus = 'Active')
group by pctf.CustProgram, CL.Name, LD.WBS1, LD.WBS2, LD.Employee, BillingDirectives.Comment, LD.PKey
-- having ((sum(LD.RegHrs) > 0) or (sum(LD.OvtHrs) > 0) or (sum(LD.SpecialOvtHrs) > 0))
order by pctf.CustProgram, CL.Name, LD.WBS1, WBS2, EMName

I need to find written off hours for each Employee, WBS1, WBS2 combination.

I have tried a dozen different things with that having clause and cannot get it to give me an accurate result.

Upvotes: 1

Views: 26

Answers (1)

Bohemian
Bohemian

Reputation: 425003

Use a case inside the sum():

select blah, blah, 
  sum(
    case when LD.RegHrs > 0 then LD.RegHrs else 0 end +
    case when LD.OvtHrs > 0 then LD.OvtHrs else 0 end +
    case when LD.SpecialOvtHrs > 0 then LD.SpecialOvtHrs else 0 end
  ) as [Hours], blah, blah
from blah join blah ...
group by blah, blah
-- no having clause

As a mathematical curiosity, you could also code the sum this way:

sum((LD.RegHrs + abs(LD.RegHrs) +
    (LD.OvtHrs + abs(LD.OvtHrs) + 
    (LD.SpecialOvtHrs + abs(LD.SpecialOvtHrs)) / 2

which although is a bit less readable, it uses less code and may impress your colleagues more :)

Upvotes: 1

Related Questions