| Author |
 Topic  |
|
jctech
Starting Member
USA
28 Posts |
 Posted - 07/23/2012 : 16:17:20
|
Hello,
I have a query below that I am trying to add a 2nd Category to without any luck. I am trying to get only one line per employid but the RT and OT are creating two lines for each employid, so I would like the payrate column to go away and only show a payrate-OT and a payrate-RT. I would want to use the table PAYROLCD as a 2nd category and have a PIVOT(SUM(PAYRATE)) line used somehow...the below is what I have currently. Any help would be greatly appreciated...thanks in advance!
select *
from (
SELECT employid,deprtmnt,jobtitle,chekdate,payrate,UNTSTOPY,Category
FROM (
SELECT employid,deprtmnt,jobtitle,chekdate,payrate,UNTSTOPY,payrolcd as category
FROM [upr30300]
) t
where Category in ('H-RT','H-OT','H-PTO','H-SICK','H-BERV')
) d
PIVOT(SUM(UNTSTOPY) FOR Category IN ([H-RT],[H-OT],[H-PTO],[H-SICK],[H-BERV]))p |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/23/2012 : 16:41:41
|
sorry your explanation doesnt make much sense. can you post some sample data and explain what you want showing the output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/23/2012 : 17:50:23
|
Sorry. The column results currently show as is:
employid, deprtmnt, jobtitle, chekdate, payrate, h-rt, h-ot, h-pto, h-sick, h-berv
The payrate column is resulting in two rows for one employee for a specific chekdate and I would like only one row.
I think removing the chekdate column and instead inserting a "payrate-OT" and a "payrate-RT" column would do the trick like this:
employid, deprtmnt, jobtitle, chekdate, payrate-ot, payrate-rt, h-rt, h-ot, h-pto, h-sick, h-berv
Is there a way to insert a category1(h-rt,h-ot) and a category2(h-rt,h-ot,h-pto,h-sick,h-berv) where I can have two pivots? One for the existing
"PIVOT(SUM(UNTSTOPY) FOR Category IN ([H-RT],[H-OT],[H-PTO],[H-SICK],[H-BERV]))p"
and the other for a
"PIVOT(SUM(PAYRATE) FOR Category IN ([H-RT],[H-OT]))q"? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/23/2012 : 21:02:38
|
do you mean this?
SELECT employid,deprtmnt,jobtitle,
SUM(CASE WHEN payrolcd = 'H-RT' THEN payrate ELSE 0 END) AS [H-RTPay],
SUM(CASE WHEN payrolcd = 'H-OT' THEN payrate ELSE 0 END) AS [H-OTPay],
SUM(CASE WHEN payrolcd = 'H-RT' THEN UNTSTOPY ELSE 0 END) AS [H-RTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-OT' THEN UNTSTOPY ELSE 0 END) AS [H-OTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-PTO' THEN UNTSTOPY ELSE 0 END) AS [H-PTOUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-SICK' THEN UNTSTOPY ELSE 0 END) AS [H-SICKUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-BERV' THEN UNTSTOPY ELSE 0 END) AS [H-BERVUNTSTOPY]
FROM [upr30300]
GROUP BY employid,deprtmnt,jobtitle
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 12:25:27
|
Hello visakh16,
Thank you for your help~ It would be like the above except with a chekdate.
When I try to include a chekdate, it says, "Incorrect syntax near the keyword 'CASE'. |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 12:26:36
|
Would I be able to somehow join the original query with this one below?
select *
from (
SELECT employid,deprtmnt,jobtitle,chekdate,payrate,Category
FROM (
SELECT employid,deprtmnt,jobtitle,chekdate,PAYRATE,payrolcd as category
FROM [upr30300]
) t
where Category in ('H-RT','H-OT')
) d
PIVOT(SUM(payrate) FOR Category IN ([H-RT],[H-OT]))p |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 12:28:44
|
quote:
Originally posted by jctech
Hello visakh16,
Thank you for your help~ It would be like the above except with a chekdate.
When I try to include a chekdate, it says, "Incorrect syntax near the keyword 'CASE'.
can you show your required output including checkdate?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 12:45:02
|
employid, deprtmnt, jobtitle, chekdate, H-OTpay, H-RTpay, h-rt, h-ot, h-pto, h-sick, h-berv
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 13:01:42
|
SELECT employid,deprtmnt,jobtitle,chekdate,
SUM(CASE WHEN payrolcd = 'H-RT' THEN payrate ELSE 0 END) AS [H-RTPay],
SUM(CASE WHEN payrolcd = 'H-OT' THEN payrate ELSE 0 END) AS [H-OTPay],
SUM(CASE WHEN payrolcd = 'H-RT' THEN UNTSTOPY ELSE 0 END) AS [H-RTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-OT' THEN UNTSTOPY ELSE 0 END) AS [H-OTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-PTO' THEN UNTSTOPY ELSE 0 END) AS [H-PTOUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-SICK' THEN UNTSTOPY ELSE 0 END) AS [H-SICKUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-BERV' THEN UNTSTOPY ELSE 0 END) AS [H-BERVUNTSTOPY]
FROM [upr30300]
GROUP BY employid,deprtmnt,jobtitle,checkdate
if chekdate has timepart also make sure you do this modification
SELECT employid,deprtmnt,jobtitle,DATEADD(dd,DATEDIFF(dd,0,checkdate),0) AS chekdate,
SUM(CASE WHEN payrolcd = 'H-RT' THEN payrate ELSE 0 END) AS [H-RTPay],
SUM(CASE WHEN payrolcd = 'H-OT' THEN payrate ELSE 0 END) AS [H-OTPay],
SUM(CASE WHEN payrolcd = 'H-RT' THEN UNTSTOPY ELSE 0 END) AS [H-RTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-OT' THEN UNTSTOPY ELSE 0 END) AS [H-OTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-PTO' THEN UNTSTOPY ELSE 0 END) AS [H-PTOUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-SICK' THEN UNTSTOPY ELSE 0 END) AS [H-SICKUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-BERV' THEN UNTSTOPY ELSE 0 END) AS [H-BERVUNTSTOPY]
FROM [upr30300]
GROUP BY employid,deprtmnt,jobtitle,DATEADD(dd,DATEDIFF(dd,0,checkdate),0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 13:09:26
|
Visakh16, you are awesome! BTW, are most of these queries something I can learn picking up a couple T-SQL books? Well...that with decades of experience doing it...? Do you have an existing post of "how to get to the level I'm at"?  |
Edited by - jctech on 07/24/2012 13:20:56 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 13:26:30
|
quote:
Originally posted by jctech
Visakh16, you are awesome! BTW, are most of these queries something I can learn picking up a couple T-SQL books? Well...that with decades of experience doing it...? Do you have an existing post of "how to get to the level I'm at"?
You can refer lots of posts on crosstabbing that people have written on for getting logic like above.
For the latter part I dont feel like there's much to write on that
Its just that you just need to get exposure on as much scenarios as above and you can get them from sites like this
and make sure you learn basis first by refering a good sql online tutorial or a book. In case of doubts post and there are people to help you out.
the bottom line is practice as much as you can and get the interest in technology and you can certainly reach much higher level than me
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 07/24/2012 13:26:56 |
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 14:09:30
|
Thank you for the encouragement. This is kind of intimidating for me because there seems to be so much to learn!
On a side note, how would I filter out the results where all of the SUM's are showing zeros? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 14:20:02
|
quote:
Originally posted by jctech
Thank you for the encouragement. This is kind of intimidating for me because there seems to be so much to learn!
On a side note, how would I filter out the results where all of the SUM's are showing zeros?
just add a where condition
SELECT employid,deprtmnt,jobtitle,DATEADD(dd,DATEDIFF(dd,0,checkdate),0) AS chekdate,
SUM(CASE WHEN payrolcd = 'H-RT' THEN payrate ELSE 0 END) AS [H-RTPay],
SUM(CASE WHEN payrolcd = 'H-OT' THEN payrate ELSE 0 END) AS [H-OTPay],
SUM(CASE WHEN payrolcd = 'H-RT' THEN UNTSTOPY ELSE 0 END) AS [H-RTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-OT' THEN UNTSTOPY ELSE 0 END) AS [H-OTUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-PTO' THEN UNTSTOPY ELSE 0 END) AS [H-PTOUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-SICK' THEN UNTSTOPY ELSE 0 END) AS [H-SICKUNTSTOPY],
SUM(CASE WHEN payrolcd = 'H-BERV' THEN UNTSTOPY ELSE 0 END) AS [H-BERVUNTSTOPY]
FROM [upr30300]
WHERE payrolcd IN ('H-RT' ,'H-OT','H-PTO','H-SICK','H-BERV')
GROUP BY employid,deprtmnt,jobtitle,DATEADD(dd,DATEDIFF(dd,0,checkdate),0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 14:37:47
|
| Hmmm it works but that doesn't make sense to me. I thought it had to be a "where payrolcd in ('H-RT' ,'H-OT','H-PTO','H-SICK','H-BERV')<> 0 or something along those lines... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 14:42:25
|
quote:
Originally posted by jctech
Hmmm it works but that doesn't make sense to me. I thought it had to be a "where payrolcd in ('H-RT' ,'H-OT','H-PTO','H-SICK','H-BERV')<> 0 or something along those lines...
thats if you're doing it after summing
i'm filtering the data using where even before you sum the data hence just where condition to restrict data to required categories
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 14:57:03
|
| Ohhh I see. That makes sense. I've got a long ways to go...thanks again visakh16~ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 15:10:03
|
no probs...Glad to help you out always...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/24/2012 : 17:06:17
|
visakh16, I am seeing some payrates that are adding up for the same paydate.
For example: hourly payrate of $15.00 would show as $45.00. I'm not sure how to fix this... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/24/2012 : 23:09:13
|
quote:
Originally posted by jctech
visakh16, I am seeing some payrates that are adding up for the same paydate.
For example: hourly payrate of $15.00 would show as $45.00. I'm not sure how to fix this...
I cant suggest anything unless I see how your data is. Are you telling you've duplicate records for the same date?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jctech
Starting Member
USA
28 Posts |
Posted - 07/25/2012 : 12:07:18
|
Yes, there are some cases where a user would have two regular paycodes(RT) for the same date. For example, when a user receives a pay increase that is effective on the 2nd week of the pay period.
Maybe I can query both with the Category and a SUM/CASE function. |
Edited by - jctech on 07/25/2012 12:12:45 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/25/2012 : 12:14:23
|
quote:
Originally posted by jctech
Yes, there are some cases where a user would have two regular paycodes(RT) for the same date. For example, when a user receives a pay increase that is effective on the 2nd week of the pay period.
Maybe I can query both with the Category and a SUM/CASE function.
ok so in that case how should be your output? you want to include only one of those records? ie as per latest pay rate?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Topic |
|
|
|
help with two category 'functions'? within a query
