Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

15 minutes Interval from Hours - Please Help Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 10:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Jeff Moden (2/17/2014)
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
zulfansari (2/17/2014)
Hi Jeff, Dwain.c and SQLRNNR,

You guys are AWESOME..

I really appreciate your help and learned new tips and tricks from all of you..

God bless you all!

Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..

Best Regards,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


Who is Red Green? Any relation to Opal Azure?


I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.
http://en.wikipedia.org/wiki/The_Red_Green_Show


Looks like the Canuck version of Hee Haw



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542367
Posted Tuesday, February 18, 2014 11:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 2,660, Visits: 19,195
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
zulfansari (2/17/2014)
Hi Jeff, Dwain.c and SQLRNNR,

You guys are AWESOME..

I really appreciate your help and learned new tips and tricks from all of you..

God bless you all!

Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..

Best Regards,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


Who is Red Green? Any relation to Opal Azure?


I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.
http://en.wikipedia.org/wiki/The_Red_Green_Show


Looks like the Canuck version of The Bachelor
There, fixed that for you. And I didn't even use duct tape!


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #1542689
Posted Tuesday, February 18, 2014 4:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:31 PM
Points: 66, Visits: 258
Hi Guys,

Hoping you could help out again, I've been working on this all day long but no luck.

I got the query in the system, based on the following results, can you kindly help me add the last column to the right that holds the running total? I need to be able to see when the double paycode is at 9 hours then the remaining hours will go into Triple paycode.

 SELECT PERSON,CONVERT(VARCHAR(10), DATE,101) DATE,HOURS = t.N*.25,PAYCODE
FROM Payroll_Detail_OT
CROSS JOIN dbo.Tally t
WHERE t.N <= HOURS*4

(Hi Jeff, I don't know how to post the results like you did so I'm sorry if the results look all messed up)

PERSON DATE HOURS PAYCODE TOTAL
1125 27/01/2014 0.25 Double 0.25
1125 27/01/2014 0.50 Double 0.50
1125 27/01/2014 0.75 Double 0.75
1125 27/01/2014 1.00 Double 1.00
1125 27/01/2014 1.25 Double 1.25
1125 27/01/2014 1.50 Double 1.50
1125 27/01/2014 1.75 Double 1.75
1125 27/01/2014 2.00 Double 2.00
1125 27/01/2014 2.25 Double 2.25
1125 27/01/2014 2.50 Double 2.50
1125 27/01/2014 2.75 Double 2.75
1125 27/01/2014 3.00 Double 3.00
1125 28/01/2014 0.25 Double 3.25
1125 28/01/2014 0.50 Double 3.50
1125 28/01/2014 0.75 Double 3.75
1125 28/01/2014 1.00 Double 4.00
1125 28/01/2014 1.25 Double 4.25
1125 28/01/2014 1.50 Double 4.50
1125 28/01/2014 1.75 Double 4.75
1125 28/01/2014 2.00 Double 5.00
1125 28/01/2014 2.25 Double 5.25
1125 28/01/2014 2.50 Double 5.50
1125 29/01/2014 0.25 Double 5.75
1125 29/01/2014 0.50 Double 6.00
1125 29/01/2014 0.75 Double 6.25
1125 29/01/2014 1.00 Double 6.50
1125 29/01/2014 1.25 Double 6.75
1125 29/01/2014 1.50 Double 7.00
1125 29/01/2014 1.75 Double 7.25
1125 29/01/2014 2.00 Double 7.50
1125 29/01/2014 2.25 Double 7.75
1125 29/01/2014 2.50 Double 8.00
1125 29/01/2014 2.75 Double 8.20
1125 29/01/2014 3.00 Double 8.50
1125 30/01/2014 0.25 Double 8.75
1125 30/01/2014 0.50 Double 9.00
1125 30/01/2014 0.75 Double 9.25
1125 30/01/2014 1.00 Double 9.50
1125 30/01/2014 1.25 Double 9.75
1125 30/01/2014 1.50 Double 10.00
1125 30/01/2014 1.75 Double 10.25
1125 30/01/2014 2.00 Double 10.50
1125 30/01/2014 2.25 Double 10.75
1125 30/01/2014 2.50 Double 11.00
1125 30/01/2014 2.75 Double 11.25
1125 30/01/2014 3.00 Double 11.50

So the final query should give the following result:

PERSON DATE HOURS PAYCODE TOTAL
1125 30/01/2014 0.25 Double 9.00
1125 30/01/2014 0.50 Triple 2.50
Post #1542790
Posted Tuesday, February 18, 2014 5:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
jcrawf02 (2/18/2014)
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
zulfansari (2/17/2014)
Hi Jeff, Dwain.c and SQLRNNR,

You guys are AWESOME..

I really appreciate your help and learned new tips and tricks from all of you..

God bless you all!

Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..

Best Regards,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


Who is Red Green? Any relation to Opal Azure?


I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.
http://en.wikipedia.org/wiki/The_Red_Green_Show


Looks like the Canuck version of The Bachelor
There, fixed that for you. And I didn't even use duct tape!


Hey wait a minute! You're implying that I've watched that awful program! I resemble that.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542802
Posted Tuesday, February 18, 2014 5:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
zulfansari (2/18/2014)
Hi Guys,

Hoping you could help out again, I've been working on this all day long but no luck.

I got the query in the system, based on the following results, can you kindly help me add the last column to the right that holds the running total? I need to be able to see when the double paycode is at 9 hours then the remaining hours will go into Triple paycode.

 SELECT PERSON,CONVERT(VARCHAR(10), DATE,101) DATE,HOURS = t.N*.25,PAYCODE
FROM Payroll_Detail_OT
CROSS JOIN dbo.Tally t
WHERE t.N <= HOURS*4

(Hi Jeff, I don't know how to post the results like you did so I'm sorry if the results look all messed up)

PERSON DATE HOURS PAYCODE TOTAL
1125 27/01/2014 0.25 Double 0.25
1125 27/01/2014 0.50 Double 0.50
1125 27/01/2014 0.75 Double 0.75
1125 27/01/2014 1.00 Double 1.00
1125 27/01/2014 1.25 Double 1.25
1125 27/01/2014 1.50 Double 1.50
1125 27/01/2014 1.75 Double 1.75
1125 27/01/2014 2.00 Double 2.00
1125 27/01/2014 2.25 Double 2.25
1125 27/01/2014 2.50 Double 2.50
1125 27/01/2014 2.75 Double 2.75
1125 27/01/2014 3.00 Double 3.00
1125 28/01/2014 0.25 Double 3.25
1125 28/01/2014 0.50 Double 3.50
1125 28/01/2014 0.75 Double 3.75
1125 28/01/2014 1.00 Double 4.00
1125 28/01/2014 1.25 Double 4.25
1125 28/01/2014 1.50 Double 4.50
1125 28/01/2014 1.75 Double 4.75
1125 28/01/2014 2.00 Double 5.00
1125 28/01/2014 2.25 Double 5.25
1125 28/01/2014 2.50 Double 5.50
1125 29/01/2014 0.25 Double 5.75
1125 29/01/2014 0.50 Double 6.00
1125 29/01/2014 0.75 Double 6.25
1125 29/01/2014 1.00 Double 6.50
1125 29/01/2014 1.25 Double 6.75
1125 29/01/2014 1.50 Double 7.00
1125 29/01/2014 1.75 Double 7.25
1125 29/01/2014 2.00 Double 7.50
1125 29/01/2014 2.25 Double 7.75
1125 29/01/2014 2.50 Double 8.00
1125 29/01/2014 2.75 Double 8.20
1125 29/01/2014 3.00 Double 8.50
1125 30/01/2014 0.25 Double 8.75
1125 30/01/2014 0.50 Double 9.00
1125 30/01/2014 0.75 Double 9.25
1125 30/01/2014 1.00 Double 9.50
1125 30/01/2014 1.25 Double 9.75
1125 30/01/2014 1.50 Double 10.00
1125 30/01/2014 1.75 Double 10.25
1125 30/01/2014 2.00 Double 10.50
1125 30/01/2014 2.25 Double 10.75
1125 30/01/2014 2.50 Double 11.00
1125 30/01/2014 2.75 Double 11.25
1125 30/01/2014 3.00 Double 11.50

So the final query should give the following result:

PERSON DATE HOURS PAYCODE TOTAL
1125 30/01/2014 0.25 Double 9.00
1125 30/01/2014 0.50 Triple 2.50


First off, as you're relatively new here, let me show how you should be posting sample data. You can use the IFCode Shortcuts on the left of the "Post Reoly" web page to make it look like SQL.

WITH SampleData (PERSON, [DATE], [HOURS], PAYCODE, TOTAL) AS
(
SELECT 1125,'30/01/2014',0.25,'Double',8.75
UNION ALL SELECT 1125,'2014-01-30',0.50,'Double',9.00
UNION ALL SELECT 1125,'2014-01-30',0.75,'Double',9.25
UNION ALL SELECT 1125,'2014-01-30',1.00,'Double',9.50
UNION ALL SELECT 1125,'2014-01-30',1.25,'Double',9.75
UNION ALL SELECT 1125,'2014-01-30',1.50,'Double',10.00
UNION ALL SELECT 1125,'2014-01-30',1.75,'Double',10.25
UNION ALL SELECT 1125,'2014-01-30',2.00,'Double',10.50
UNION ALL SELECT 1125,'2014-01-30',2.25,'Double',10.75
UNION ALL SELECT 1125,'2014-01-30',2.50,'Double',11.00
UNION ALL SELECT 1125,'2014-01-30',2.75,'Double',11.25
UNION ALL SELECT 1125,'2014-01-30',3.00,'Double',11.50
)
SELECT *
FROM SampleData;


Now you're going to need to help me out a little. Can you explain how the first 2 rows of that sample data gets translated to this (your expected result):

PERSON    DATE        HOURS   PAYCODE  TOTAL
1125 30/01/2014 0.25 Double 9.00
1125 30/01/2014 0.50 Triple 2.50


Sorry but I'm not getting it from the description you provided.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542810
Posted Tuesday, February 18, 2014 7:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:36 PM
Points: 21,285, Visits: 14,979
dwain.c (2/18/2014)


Now you're going to need to help me out a little. Can you explain how the first 2 rows of that sample data gets translated to this (your expected result):

PERSON    DATE        HOURS   PAYCODE  TOTAL
1125 30/01/2014 0.25 Double 9.00
1125 30/01/2014 0.50 Triple 2.50


Sorry but I'm not getting it from the description you provided.


I'm in the same boat - not getting the connection between the description and the sample output provided.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1542819
Posted Tuesday, February 18, 2014 7:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:31 PM
Points: 66, Visits: 258
Hi Dwain,

From the sample data I need to check if the Double Paycode has reached 9 hours, once it has, I use that date as the effective date with 9 hours for Double paycode, then anything over 9 hours of double go in Triple paycode. It just so happens that on 30/01/2014 this employee reached 9 hours threshold for Double so that's why employee get 9 hours for Double and remainder hours in the sample data goes to Triple.

As far as the HOURS column is concerned it's there to breakdown the hours in 15 minutes increment, I can ignore the HOURS column since I don't need it for the report.

I hope this is what you were looking for, I can provide any other detail..

Thank you very much for your help..

PERSON  DATE         HOURS  PAYCODE  TOTAL
1125 30/01/2014 0.50 Double 9.00
1125 30/01/2014 0.75 Triple 2.50

Post #1542825
Posted Tuesday, February 18, 2014 8:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:31 PM
Points: 66, Visits: 258
Sorry I forgot to add the sample data.

WITH SampleData (PERSON, [DATE], [HOURS], PAYCODE, TOTAL) AS
(
SELECT 1125,'27/01/2014',0.25,'Double',0.25
UNION ALL SELECT 1125,'27/01/2014',0.50,'Double',0.50
UNION ALL SELECT 1125,'27/01/2014',0.75,'Double',0.75
UNION ALL SELECT 1125,'27/01/2014',1.00,'Double',1.00
UNION ALL SELECT 1125,'27/01/2014',1.25,'Double',1.25
UNION ALL SELECT 1125,'27/01/2014',1.50,'Double',1.50
UNION ALL SELECT 1125,'27/01/2014',1.75,'Double',1.75
UNION ALL SELECT 1125,'27/01/2014',2.00,'Double',2.00
UNION ALL SELECT 1125,'27/01/2014',2.25,'Double',2.25
UNION ALL SELECT 1125,'27/01/2014',2.50,'Double',2.50
UNION ALL SELECT 1125,'27/01/2014',2.75,'Double',2.75
UNION ALL SELECT 1125,'27/01/2014',3.00,'Double',3.00
UNION ALL SELECT 1125,'28/01/2014',0.25,'Double',3.25
UNION ALL SELECT 1125,'28/01/2014',0.50,'Double',3.50
UNION ALL SELECT 1125,'28/01/2014',0.75,'Double',3.75
UNION ALL SELECT 1125,'28/01/2014',1.00,'Double',4.00
UNION ALL SELECT 1125,'28/01/2014',1.25,'Double',4.25
UNION ALL SELECT 1125,'28/01/2014',1.50,'Double',4.50
UNION ALL SELECT 1125,'28/01/2014',1.75,'Double',4.75
UNION ALL SELECT 1125,'28/01/2014',2.00,'Double',5.00
UNION ALL SELECT 1125,'28/01/2014',2.25,'Double',5.25
UNION ALL SELECT 1125,'28/01/2014',2.50,'Double',5.50
UNION ALL SELECT 1125,'29/01/2014',0.25,'Double',5.75
UNION ALL SELECT 1125,'29/01/2014',0.50,'Double',6.00
UNION ALL SELECT 1125,'29/01/2014',0.75,'Double',6.25
UNION ALL SELECT 1125,'29/01/2014',1.00,'Double',6.50
UNION ALL SELECT 1125,'29/01/2014',1.25,'Double',6.75
UNION ALL SELECT 1125,'29/01/2014',1.50,'Double',7.00
UNION ALL SELECT 1125,'29/01/2014',1.75,'Double',7.25
UNION ALL SELECT 1125,'29/01/2014',2.00,'Double',7.50
UNION ALL SELECT 1125,'29/01/2014',2.25,'Double',7.75
UNION ALL SELECT 1125,'29/01/2014',2.50,'Double',8.00
UNION ALL SELECT 1125,'29/01/2014',2.75,'Double',8.25
UNION ALL SELECT 1125,'29/01/2014',3.00,'Double',8.50
UNION ALL SELECT 1125,'30/01/2014',0.25,'Double',8.75
UNION ALL SELECT 1125,'30/01/2014',0.50,'Double',9.00
UNION ALL SELECT 1125,'30/01/2014',0.75,'Double',9.25
UNION ALL SELECT 1125,'30/01/2014',1.00,'Double',9.50
UNION ALL SELECT 1125,'30/01/2014',1.25,'Double',9.75
UNION ALL SELECT 1125,'30/01/2014',1.50,'Double',10.00
UNION ALL SELECT 1125,'30/01/2014',1.75,'Double',10.25
UNION ALL SELECT 1125,'30/01/2014',2.00,'Double',10.50
UNION ALL SELECT 1125,'30/01/2014',2.25,'Double',10.75
UNION ALL SELECT 1125,'30/01/2014',2.50,'Double',11.00
UNION ALL SELECT 1125,'30/01/2014',2.75,'Double',11.25
UNION ALL SELECT 1125,'30/01/2014',3.00,'Double',11.50
)
SELECT *
FROM SampleData;

Post #1542827
Posted Tuesday, February 18, 2014 8:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
zulfansari (2/18/2014)
Hi Dwain,

From the sample data I need to check if the Double Paycode has reached 9 hours, once it has, I use that date as the effective date with 9 hours for Double paycode, then anything over 9 hours of double go in Triple paycode. It just so happens that on 30/01/2014 this employee reached 9 hours threshold for Double so that's why employee get 9 hours for Double and remainder hours in the sample data goes to Triple.

As far as the HOURS column is concerned it's there to breakdown the hours in 15 minutes increment, I can ignore the HOURS column since I don't need it for the report.

I hope this is what you were looking for, I can provide any other detail..

Thank you very much for your help..

PERSON  DATE         HOURS  PAYCODE  TOTAL
1125 30/01/2014 0.50 Double 9.00
1125 30/01/2014 0.75 Triple 2.50



Sorry I'm still confused. Let's try some more questions:

- Are you expecting only 2 rows in your final output? If you expect anything other than 2 rows, please show all as your expected results.
- Where does the 2.5 value come from in the TOTAL column for Triple? I know you said it is the remainder, but I can't see the remainder of what. Which specific rows is the 2.5 calculated from?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542828
Posted Tuesday, February 18, 2014 8:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:31 PM
Points: 66, Visits: 258
Hi Guys,

I'm sorry about the confusion, I guess I should take it step by step, first I need to add a total column as the last column so then I can count when the total number of hours for Double reaches 9. Once I have the total column going then I can worry about the triple paycode thing I mentioned earlier.

Right now my SQL query produce results in shown in section A below, I need to edit the query so I can get results shown in section B.

My Payroll_Detail_OT table has the same data as the sample data I provided in last post.

SQL Query

 SELECT PERSON,CONVERT(VARCHAR(10), DATE,101) DATE,HOURS = t.N*.25,PAYCODE
FROM Payroll_Detail_OT
CROSS JOIN dbo.Tally t
WHERE t.N <= HOURS*4

Section A - Current Results

PERSON  DATE         HOURS  PAYCODE
1125 27/01/2014 0.25 Double
1125 27/01/2014 0.50 Double
1125 27/01/2014 0.75 Double
1125 27/01/2014 1.00 Double
1125 27/01/2014 1.25 Double
1125 27/01/2014 1.50 Double
1125 27/01/2014 1.75 Double
1125 27/01/2014 2.00 Double
1125 27/01/2014 2.25 Double
1125 27/01/2014 2.50 Double
1125 27/01/2014 2.75 Double
1125 27/01/2014 3.00 Double
1125 28/01/2014 0.25 Double
1125 28/01/2014 0.50 Double
1125 28/01/2014 0.75 Double
1125 28/01/2014 1.00 Double
1125 28/01/2014 1.25 Double
1125 28/01/2014 1.50 Double
1125 28/01/2014 1.75 Double
1125 28/01/2014 2.00 Double
1125 28/01/2014 2.25 Double
1125 28/01/2014 2.50 Double


Section B - Desired Result

PERSON  DATE         HOURS  PAYCODE  TOTAL
1125 27/01/2014 0.25 Double 0.25
1125 27/01/2014 0.50 Double 0.50
1125 27/01/2014 0.75 Double 0.75
1125 27/01/2014 1.00 Double 1.00
1125 27/01/2014 1.25 Double 1.25
1125 27/01/2014 1.50 Double 1.50
1125 27/01/2014 1.75 Double 1.75
1125 27/01/2014 2.00 Double 2.00
1125 27/01/2014 2.25 Double 2.25
1125 27/01/2014 2.50 Double 2.50
1125 27/01/2014 2.75 Double 2.75
1125 27/01/2014 3.00 Double 3.00
1125 28/01/2014 0.25 Double 3.25
1125 28/01/2014 0.50 Double 3.50
1125 28/01/2014 0.75 Double 3.75
1125 28/01/2014 1.00 Double 4.00
1125 28/01/2014 1.25 Double 4.25
1125 28/01/2014 1.50 Double 4.50
1125 28/01/2014 1.75 Double 4.75
1125 28/01/2014 2.00 Double 5.00
1125 28/01/2014 2.25 Double 5.25
1125 28/01/2014 2.50 Double 5.50


Post #1542831
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse