Making two records into one

15 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 ?

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Fscopel
    Good point! I got rid of the DISTINCT, and I used MAX instead of SUM. Thank you for you help.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following