Making two records into one
Home > IT Answers > Database > Making two records into one
Fscopel
15 pts.
0
Q:
Making two records into one
CASE statement, SQL Records
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
ASKED: May 22 2009  7:16 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29820 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
How about something like this:

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


But, why do you need the DISTINCT keyword ?
Last Answered: May 22 2009  8:49 PM GMT by Carlosdl   29820 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Fscopel   15 pts.  |   May 26 2009  1:42PM GMT

Good point!
I got rid of the DISTINCT, and I used MAX instead of SUM.

Thank you for you help.

 
0