help with another query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with another query

Author

Topic

jctech
Starting Member

28 Posts

Posted - 2012-07-06 : 19:15:33

Hello,
I have a query I was hoping to get some help with...
I am trying to combine three summed columns(401k50,401-k%,401-k) into one and these two(401kln,401kl2) into one. Ultimately, I would want 401k, roth, loans. Here is the query below that I'm not able to modify....thanks in advance.

select *
from (
SELECT employid,chekdate,UPRTRXAM,Category
FROM (
SELECT employid,chekdate,UPRTRXAM,PAYROLCD AS Category
FROM [upr30300]
) t
where Category in ('401k50','401-k%','401-k','ROTH','401kln','401kl2')
) d
PIVOT(SUM(uprtrxam) FOR Category IN ([401k50],[401-k%],[401-k],[ROTH],[401kln],[401kl2]))p

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-06 : 21:14:36

[code]
select *, [401k50] + [401-k%] + [401-k] as NewColumn
from (
SELECT employid,chekdate,UPRTRXAM,Category
FROM (
SELECT employid,chekdate,UPRTRXAM,PAYROLCD AS Category
FROM [upr30300]
) t
where Category in ('401k50','401-k%','401-k','ROTH','401kln','401kl2')
) d
PIVOT(SUM(uprtrxam) FOR Category IN ([401k50],[401-k%],[401-k],[ROTH],[401kln],[401kl2]))p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 21:16:52

[code]
select *
from (
SELECT employid,chekdate,UPRTRXAM,Category
FROM (
SELECT employid,chekdate,UPRTRXAM,
CASE WHEN PAYROLCD IN ('401k50','401-k%','401-k') THEN '401K'
WHEN PAYROLCD IN ( '401kln','401kl2') THEN 'loan'
ELSE PAYROLCD
END AS Category
FROM [upr30300]
) t
where Category in ('401k50','401-k%','401-k','ROTH','401kln','401kl2')
) d
PIVOT(SUM(uprtrxam) FOR Category IN ('401K','ROTH','loan'))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jctech
Starting Member

28 Posts

Posted - 2012-07-09 : 11:36:56

khtan, your query results to all the original columns plus an extra column called "NewColumn" with everything as NULL...

visakh16, I'm receiving a "Incorrect syntax near '401k' for the last line...

Thank you for all your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 11:51:33

it was copy paste typo


select * 
from ( 
SELECT employid,chekdate,UPRTRXAM,Category 
FROM ( 
SELECT employid,chekdate,UPRTRXAM,
CASE WHEN PAYROLCD IN ('401k50','401-k%','401-k') THEN '401K' 
     WHEN PAYROLCD IN ( '401kln','401kl2') THEN 'loan'
     ELSE PAYROLCD 
END AS Category 
FROM [upr30300] 
) t 
where Category in ('401k50','401-k%','401-k','ROTH','401kln','401kl2') 
) d 
PIVOT(SUM(uprtrxam) FOR Category IN ([401K],[ROTH],[loan]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jctech
Starting Member

28 Posts

Posted - 2012-07-09 : 12:09:58

Oh boy, I feel sheepish.. =P
It was just a matter of the apostrophes.
I am getting query results now but the 401k and loan columns all come in NULL now.
Thank you visakh16!

jctech
Starting Member

28 Posts

Posted - 2012-07-09 : 13:00:57

I figured it out. It was the line, "where Category in ('401k50','401-k%','401-k','ROTH','401kln','401kl2')"

I just edited the categories to match 401k, roth, 401k loan.

Thank you very much visakh16. =)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 16:22:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources