Solved

SQL Query. Results sql table split into 2 columns

Posted on 2009-05-06
1
385 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

12 Experts available now in Live!

Get 1:1 Help Now