SQL Gurus! Need help PLEASE!

[Dorko]

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

[Dorko]

Anyone???

Dorko

[dmdigital]

Quick look I'd suggest you review the GROUP BY. If you are getting all the transaction table entries then you need another SUM or other grouping operation in the select statement or you are missing something from the transaction table in the GROUP BY clause.

Thanks for the reminder as to why I left IT:D

[mikehzz]

Hello,
instead of using the employee table and having a case statement, try using an inner select statement on the employee table with the case statement in that. This would leave you able to group by the result of the case as a single value.
Cheers
Mike

[Dorko]

Thanks, i will give that a go!

Cheers

Dorko

[George130]

Hello,
instead of using the employee table and having a case statement, try using an inner select statement on the employee table with the case statement in that. This would leave you able to group by the result of the case as a single value.
Cheers
Mike

As he said.
You could also build a view and then do a select on the view.
And I will stop there before you get to the game of a view built from multiple views with most fields built by functions:angel:.
We get lost in some of our logic now it's so complex.

[mikehzz]

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