Help with Grouping Problem - multiple rows per date

[WoodyGuthrie]

Hi Guys,

In this problem, I need the result to be one row per work date. Employees can have multiple pay codes for each day worked. So I need to eliminate pd.intPCode in the Group By. But how?

pd.intPCode forces multiple rows per pay-code. I need to eliminate the additional rows and have one row per work date while showing all pay code amounts on a single row.

As always, help with this is greatly appreciated.

SELECT ph.guidAID, ph.dtePayDate, 
				Case 
						When pd.intPCode IN (12, 18, 22, 59)
						Then (sum(pd.curPAmount))
						Else 0
					End As curPayProd,
				Case 
						When pd.intPCode IN (16, 33)
						Then (sum(pd.curPAmount))
						Else 0
					End As curPayFixed,
				Case 
						When pd.intPCode IN (29)
						Then (sum(pd.curPAmount))
						Else 0
					End As curPayVacation,
				Case 
						When pd.intPCode IN (69)
						Then (sum(pd.curPAmount))
						Else 0
					End As curPayOvertime	
						
FROM      tblPayHeader ph 
INNER JOIN tblPayDetail pd
ON ph.guidPayHeaderID = pd.guidPayHeaderID

WHERE  ph.dtePayDate >= '12/28/2007' 
AND  ph.dtePayDate <= '01/03/2008'
And ph.guidAID = '1DAFB36C-A17B-4193-AB49-7319D87FD0A7'

GROUP BY ph.guidAID, ph.dtePayDate, pd.intPCode, pd.curPAmount
ORDER BY ph.guidAID, ph.dtePayDate

CURRENT RESULT

1DAFB36C-A17B-4193-AB49-7319D87FD0A7 12-28-2007 0.00 50.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 12-28-2007 78.1817 0.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 01-02-2008 0.00 50.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 01-02-2008 67.2727 0.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 01-03-2008 0.00 50.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 01-03-2008 139.40 0.00 0.00 0.00
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 01-03-2008 0.00 0.00 0.00 28.49

DESIRED RESULT

1DAFB36C-A17B-4193-AB49-7319D87FD0A7 12/28/2007 78.18 50 0 0
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 1/2/2008 67.27 50 0 0
1DAFB36C-A17B-4193-AB49-7319D87FD0A7 1/3/2008 139.4 50 0 28.49

 

[r937]

just remove the last two columns from your GROUP BY clause

r937.com | rudy.ca

 

[ESquared]

this doesn't seem right, either:

Case
When pd.intPCode IN (12, 18, 22, 59)
Then (sum(pd.curPAmount))
Else 0
End

in fact I'm surprised it works. But this makes sense to me:

sum(Case
When pd.intPCode IN (12, 18, 22, 59)
Then pd.curPAmount
Else 0
End)

 

[r937]

well spotted, E²

i completely agree, and apologize for having overlooked this obvious problem

r937.com | rudy.ca