| Author |
 Topic  |
|
|
jctech
Starting Member
USA
28 Posts |
 Posted - 07/06/2012 : 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)
Singapore
16746 Posts |
Posted - 07/06/2012 : 21:14:36
|
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
KH
Time is always against us
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/06/2012 : 21:16:52
|
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
USA
28 Posts |
Posted - 07/09/2012 : 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
India
47069 Posts |
Posted - 07/09/2012 : 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
USA
28 Posts |
Posted - 07/09/2012 : 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
USA
28 Posts |
Posted - 07/09/2012 : 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
India
47069 Posts |
Posted - 07/09/2012 : 16:22:39
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
help with another query
