help with two category 'functions'? within a query

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 two category 'functions'? within a query

Author

Topic

jctech
Starting Member

28 Posts

Posted - 2012-07-23 : 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

52326 Posts

Posted - 2012-07-23 : 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

28 Posts

Posted - 2012-07-23 : 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

52326 Posts

Posted - 2012-07-23 : 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

28 Posts

Posted - 2012-07-24 : 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

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 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

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 13:01:42

[code]
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
[/code]

if chekdate has timepart also make sure you do this modification

[code]
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)
[/code]

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

jctech
Starting Member

28 Posts

Posted - 2012-07-24 : 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"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 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/

jctech
Starting Member

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 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

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 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

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 15:10:03

no probs...Glad to help you out always...

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

jctech
Starting Member

28 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 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

28 Posts

Posted - 2012-07-25 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 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/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources