Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

SQL Query. Results sql table split into 2 columns

I'm trying to query data for a report. Basically I need name, ssn, 401k % contributon, ROTH % Contibution. Problem is that 401k & ROTH are stored in the same column... The statement below is partially complete. What I need to do is add another column that returns results for ROTH, with combining 401K & ROTH.

Hopefully this makes sense... Thanks!
SELECT     UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM, SUM(UPR00500.DEDNPRCT_1) AS [401K]
FROM         UPR00500 INNER JOIN
                      UPR00100 ON UPR00500.EMPLOYID = UPR00100.EMPLOYID
WHERE     (UPR00100.INACTIVE = 0) AND (UPR00500.INACTIVE = 0) AND (UPR00500.DEDUCTON LIKE '401K%')
GROUP BY UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM

Open in new window

0
bendretread
Asked:
bendretread
1 Solution
 
reb73Commented:
Something like this, perhaps?
SELECT
	UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM,
	SUM(CASE WHEN UPR00500.DEDUCTON LIKE '401K%' THEN UPR00500.DEDNPRCT_1 ELSE 0 END) AS [401K],
	SUM(CASE WHEN UPR00500.DEDUCTON LIKE 'ROTH%' THEN UPR00500.DEDNPRCT_1 ELSE 0 END) AS [ROTH]
FROM
	UPR00500
INNER JOIN
	UPR00100 ON UPR00500.EMPLOYID = UPR00100.EMPLOYID
WHERE
	UPR00100.INACTIVE = 0 AND UPR00500.INACTIVE = 0 AND (UPR00500.DEDUCTON LIKE '401K%' OR UPR00500.DEDUCTON LIKE 'ROTH%' )
GROUP BY
	UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now