Making two records into one
I was not sure on how to word the title of my questions, so it is better if I simply give you my query and result:
SELECT DISTINCT PJ.EMPL_NUM,
(CASE WHEN PAY_TYPE = 1 THEN RATE ELSE 0 END) AS REG_RATE,
(CASE WHEN PAY_TYPE = 3 THEN RATE ELSE 0 END) AS OVR_RATE
FROM dbo.tblPayrollFlashJobDetail AS PJ
WHERE UNITNUM = 8
AND SALESDATE BETWEEN '01/01/09' AND '01/10/09'
AND EMPL_NUM = 257
ORDER BY PJ.EMPL_NUM
Result:
EMPL_NUM REG_RATE OVR_RATE
---------------------- ---------------------- ----------------------
257 0 12.975
257 8.65 0
(2 row(s) affected)
I need it to be:
EmpNUm RegRate OvrRate
257 8.65 12.975
Any ideas?
Thank you
Software/Hardware used:
Software/Hardware used:
ASKED:
May 22, 2009 7:16 PM
UPDATED:
May 26, 2009 1:42 PM
Answer Wiki:
How about something like this:
<pre>SELECT empl_num,
SUM((CASE WHEN pay_type = 1 THEN rate ELSE 0 END)) AS reg_Rate,
SUM((CASE WHEN pay_type = 3 THEN rate ELSE 0 END)) AS ovr_Rate
FROM tblPayrollFlashJobDetail
WHERE unitnum = 8
AND salesdate BETWEEN '01/01/09' AND '01/10/09'
AND empl_num = 257
GROUP BY empl_num
ORDER BY empl_num</pre>
But, why do you need the DISTINCT keyword ?
To see all answers submitted to the Answer Wiki: View Answer History.
Good point!
I got rid of the DISTINCT, and I used MAX instead of SUM.
Thank you for you help.