Solved

SQL Query. Results sql table split into 2 columns

Posted on 2009-05-06
1
384 Views
Last Modified: 2012-05-06
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
Comment
Question by:bendretread
1 Comment
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24318396
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now