Thread: SQL Gurus! Need help PLEASE!

Hi everyone,

i am bit stuck with a sql report. It should be easy, but i cant work out the join/ group by.

Whats happening is i am joining a employees table with a transaction table. In the employees table "_ipvRBEmpDetails" ED I am summing the AL balance and multiplying it by its hourly rate.

This works fine, however if i join this table with the transaction table _ipvRBEmpTransCostingALL TC i get multiple lines because the transaction table has 50 plus lines per a employee.

Problem is if i group by, i get a close result, however i still have multiple lines because i have to group by normal rate and each employee is different.

Is there a better way to join this table? My mind has gone to mush....

Thanks in advance

My code is:


SELECT

ED.[Payroll Company],
ED.Location,
CASE WHEN ED.[AL Class Code] IS NOT NULL THEN ROUND(((ED."AL Post Entitlement Hours"+ ED."AL Post Prorata Hours")- ED."AL Taken Hours")*ED.[Normal Hourly Rate],2)END As [AL Liability],
SUM(CASE WHEN TC.[Pay Comp/Add Ded Code] in ('Annual','TPr93ALCA','TPo93ALG','TALCA') THEN TC.Amount END) As [AL Expense]



FROM "_ipvRBEmpDetails" ED
LEFT JOIN _ipvRBEmpTransCostingALL TC on TC.[Employee ID]= ED.[Employee ID]

WHERE
ED.[Termination Date] IS NULL
AND TC.[Period End Date] >= '03-28-2010'
AND TC.[Period End Date] <= '04-25-2010'
AND CASE WHEN ED.[AL Class Code] IS NOT NULL THEN ROUND(((ED."AL Post Entitlement Hours"+ ED."AL Post Prorata Hours")- ED."AL Taken Hours")*ED.[Normal Hourly Rate],2) END IS NOT NULL

GROUP BY
ED.[Payroll Company],
ED.Location,
ED.[AL Class Code],
ED.[AL Post Entitlement Hours],
ED.[AL Post Prorata Hours],
ED.[AL Taken Hours],
ED.[Normal Hourly Rate]

ORDER BY
ED.[Payroll Company],
ED.Location

It should look like this (I can't test it though)-

SELECT

ED.[Payroll Company],
ED.Location,
ED.[AL Liability],
SUM(CASE WHEN TC.[Pay Comp/Add Ded Code] in ('Annual','TPr93ALCA','TPo93ALG','TALCA') THEN TC.Amount END) As [AL Expense]

FROM (SELECT [Employee ID], [Payroll Company], Location, CASE WHEN [AL Class Code] IS NOT NULL THEN ROUND((("AL Post Entitlement Hours"+ "AL Post Prorata Hours")- "AL Taken Hours")*ED.[Normal Hourly Rate],2)END As [AL Liability] from "_ipvRBEmpDetails" WHERE [Termination Date] IS NULL AND CASE WHEN [AL Class Code] IS NOT NULL THEN ROUND((("AL Post Entitlement Hours"+ "AL Post Prorata Hours")- "AL Taken Hours")*ED.[Normal Hourly Rate],2) END IS NOT NULL) ED

LEFT JOIN _ipvRBEmpTransCostingALL TC on TC.[Employee ID]= ED.[Employee ID]

WHERE
TC.[Period End Date] >= '03-28-2010'
AND TC.[Period End Date] <= '04-25-2010'

GROUP BY
ED.[Payroll Company],
ED.Location,
ED.[AL Liability],

ORDER BY
ED.[Payroll Company],
ED.Location

Mike