15 minutes Interval from Hours - Please Help

Question

15 minutes Interval from Hours - Please Help

Viewing 15 posts - 31 through 45 (of 53 total)

You must be logged in to reply to this topic. Login to reply

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 1

Hi Dwain,

Actually I should be sorry for confusing the matter 🙂

Hopefully my latest post explains a little better but let me try to answer your questions, also thank you for your patience.

- 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. [ZA] - Yes, at the end of the day two rows, one has hours for Double paycode (not to exceed 9 hours) the other row has anything over 9 hours, which in this case was 2.5.

- 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? [ZA] - So anything over the 9 hours goes into Triple, the 2.5 hours was the difference between 11.50 minus 9

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;

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Sigh. Now your desired result doesn't contain the row that says Triple.

You'd probably be better off directly answering the questions I asked, rather than reiterating what you already posted.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Not sure if this will work for your general case but it works for your sample data (I think):

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

),

AddRN AS

(

SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY TOTAL)

FROM SampleData

WHERE TOTAL >= 9

)

SELECT PERSON, [DATE], [HOURS]=MAX([HOURS])

,PAYCODE=CASE rn WHEN 1 THEN MAX(PAYCODE) ELSE 'Triple' END

,TOTAL=CASE rn WHEN 1 THEN MAX(TOTAL) ELSE MAX(Total)-9 END

FROM AddRN a

GROUP BY PERSON, [DATE], rn

HAVING rn IN (1, (SELECT COUNT(*) FROM AddRN));

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

zulfansari (2/18/2014)
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

The confusing part here is that the "Hours" starts over after it hit's 3.00. What's that all about?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 5

Hi Dwain,

That's what happen when you work on something for too long :-), you are a genius Sir, you solved one of my problems but now me being a moron today kept messing up the sample data, the sample data I've been posting had the last column as a 'desired column', I don't have the last total column yet so that's what I need to add first so then I can use your latest query to get the Double and Triple results.

This is what I have

WITH SampleData (PERSON, [DATE], [HOURS], PAYCODE) AS

(

SELECT 1125,'27/01/2014',0.25,'Double'

UNION ALL SELECT 1125,'27/01/2014',0.50,'Double'

UNION ALL SELECT 1125,'27/01/2014',0.75,'Double'

UNION ALL SELECT 1125,'27/01/2014',1.00,'Double'

UNION ALL SELECT 1125,'27/01/2014',1.25,'Double'

UNION ALL SELECT 1125,'27/01/2014',1.50,'Double'

UNION ALL SELECT 1125,'27/01/2014',1.75,'Double'

UNION ALL SELECT 1125,'27/01/2014',2.00,'Double'

UNION ALL SELECT 1125,'27/01/2014',2.25,'Double'

UNION ALL SELECT 1125,'27/01/2014',2.50,'Double'

UNION ALL SELECT 1125,'27/01/2014',2.75,'Double'

UNION ALL SELECT 1125,'27/01/2014',3.00,'Double'

UNION ALL SELECT 1125,'28/01/2014',0.25,'Double'

UNION ALL SELECT 1125,'28/01/2014',0.50,'Double'

UNION ALL SELECT 1125,'28/01/2014',0.75,'Double'

UNION ALL SELECT 1125,'28/01/2014',1.00,'Double'

UNION ALL SELECT 1125,'28/01/2014',1.25,'Double'

UNION ALL SELECT 1125,'28/01/2014',1.50,'Double'

UNION ALL SELECT 1125,'28/01/2014',1.75,'Double'

UNION ALL SELECT 1125,'28/01/2014',2.00,'Double'

UNION ALL SELECT 1125,'28/01/2014',2.25,'Double'

UNION ALL SELECT 1125,'28/01/2014',2.50,'Double'

UNION ALL SELECT 1125,'29/01/2014',0.25,'Double'

UNION ALL SELECT 1125,'29/01/2014',0.50,'Double'

UNION ALL SELECT 1125,'29/01/2014',0.75,'Double'

UNION ALL SELECT 1125,'29/01/2014',1.00,'Double'

UNION ALL SELECT 1125,'29/01/2014',1.25,'Double'

UNION ALL SELECT 1125,'29/01/2014',1.50,'Double'

UNION ALL SELECT 1125,'29/01/2014',1.75,'Double'

UNION ALL SELECT 1125,'29/01/2014',2.00,'Double'

UNION ALL SELECT 1125,'29/01/2014',2.25,'Double'

UNION ALL SELECT 1125,'29/01/2014',2.50,'Double'

UNION ALL SELECT 1125,'29/01/2014',2.75,'Double'

UNION ALL SELECT 1125,'29/01/2014',3.00,'Double'

UNION ALL SELECT 1125,'30/01/2014',0.25,'Double'

UNION ALL SELECT 1125,'30/01/2014',0.50,'Double'

UNION ALL SELECT 1125,'30/01/2014',0.75,'Double'

UNION ALL SELECT 1125,'30/01/2014',1.00,'Double'

UNION ALL SELECT 1125,'30/01/2014',1.25,'Double'

UNION ALL SELECT 1125,'30/01/2014',1.50,'Double'

UNION ALL SELECT 1125,'30/01/2014',1.75,'Double'

UNION ALL SELECT 1125,'30/01/2014',2.00,'Double'

UNION ALL SELECT 1125,'30/01/2014',2.25,'Double'

UNION ALL SELECT 1125,'30/01/2014',2.50,'Double'

UNION ALL SELECT 1125,'30/01/2014',2.75,'Double'

UNION ALL SELECT 1125,'30/01/2014',3.00,'Double'

)

SELECT *

FROM SampleData;

This is how it should be

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;

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Jeff Moden (2/18/2014)
zulfansari (2/18/2014)
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
The confusing part here is that the "Hours" starts over after it hit's 3.00. What's that all about?

Change of date?

OP did say to ignore the HOURS column. But I agree that these requirements represent one seriously wacky time capture system.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 7

Agree with both of you 🙂

So you think it's possible to add the last Total column?

Thank you again guys.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

OK Z, here's the deal. I started getting the impression that you were walking us through the solution in the way that you thought it should be solved, leading us down the garden path so to speak. Correct me if I'm wrong after you run the code I provided.

So I went back to your original post to see if it contained your actual payroll detail records, which it did. I believe I now understand what you're trying to do and I think the solution path you are thinking of is incorrect. So here's my take. Read the comments in the code.

-- Temp table has a clustered index to support the way

-- we'll calculate your running total.

CREATE TABLE #Temp

(

PERSON INT NOT NULL

,[DATE] DATE NOT NULL

,[HOURS] DECIMAL(5,2) NOT NULL

,CumHrs DECIMAL(5,2) NULL

,CONSTRAINT t_pk PRIMARY KEY(PERSON, [DATE])

);

-- The CTE represents what I think you've got in your Payroll Detail table.

-- I've added a couple of employees

WITH Payroll_Detail (PERSON, [DATE], [HOURS]) AS

(

-- First employee - running total > 9

SELECT 1125,CAST('2014-01-27' AS DATE),3.00

UNION ALL SELECT 1125,'2014-01-28',2.50

UNION ALL SELECT 1125,'2014-01-29',3.00

UNION ALL SELECT 1125,'2014-01-30',3.00

-- Second employee - running total = 9

UNION ALL SELECT 1126,'2014-01-27',2.00

UNION ALL SELECT 1126,'2014-01-28',2.50

UNION ALL SELECT 1126,'2014-01-29',3.00

UNION ALL SELECT 1126,'2014-01-30',1.50

-- I am not sure what to return for this guy, who's got less than

-- 9 hours. I return nothing but that could be changed.

UNION ALL SELECT 1127,'2014-01-27',2.00

UNION ALL SELECT 1127,'2014-01-28',2.50

UNION ALL SELECT 1127,'2014-01-29',3.00

UNION ALL SELECT 1127,'2014-01-30',1.00

)

-- Populate the temp table

INSERT INTO #Temp (PERSON, [DATE], [HOURS])

SELECT *

FROM Payroll_Detail;

-- Set up for the Quirky Update (QU) to calculate running totals

DECLARE @RunningTotal DECIMAL(5,2) = 0

,@Person INT = 0;

-- QU that calculates the running total. Query hints (WITH, OPTION) are important.

UPDATE #Temp WITH(TABLOCKX)

SET @RunningTotal = CumHrs = CASE PERSON WHEN @Person THEN @RunningTotal + [Hours] ELSE [Hours] END

,@Person = PERSON

OPTION (MAXDOP 1);

-- Now we retrieve what I think you want from the temp table

SELECT PERSON, [DATE], [HOURS]

,[PAYCODE]

,[TOTAL]

FROM

(

SELECT *

,rn=CASE WHEN CumHrs >= 9 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY [DATE]) END

FROM #Temp a

CROSS APPLY (VALUES('Double'),('Triple')) b([PAYCODE])

) a

CROSS APPLY

(

SELECT [TOTAL]=CASE WHEN [PAYCODE] = 'Double' AND CumHrs <= 9 THEN CumHrs

WHEN [PAYCODE] = 'Double' THEN 9

ELSE CumHrs - 9 END

) b

WHERE rn IS NULL AND [TOTAL] > 0;

GO

DROP TABLE #Temp

The only question really is what to return for employee 1127 when he's got less than 9 hours. I chose to return nothing.

Edit: You MUST read this article if you're going to use the QU method to calculate running totals.

Solving the Running Total and Ordinal Rank Problems[/url]. As the author, SQL MVP Jeff Moden likes to say it is "nasty fast."

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 9

Hi Dwaine,

You are amazing, how do you come with such codes? 🙂

When cumulative Double total is less than 9 then you are right hours stay as they are.

Can I ask you a dumb question?

If I already have data in a temp table, then how would I use your earlier supplied query without using the 'WITH SampleData (PERSON, [DATE], [HOURS], PAYCODE, TOTAL) AS' syntax, instead I want to use straight Select statement..

/** Here can I use something like this instead of using a WITH AS? **/

(SELECT * FROM PAYROLL_DETAIL

),

AddRN AS

(

SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY TOTAL)

FROM PAYROLL_DETAIL

WHERE TOTAL >= 9

)

SELECT PERSON, [DATE], [HOURS]=MAX([HOURS])

,PAYCODE=CASE rn WHEN 1 THEN MAX(PAYCODE) ELSE 'Triple' END

,TOTAL=CASE rn WHEN 1 THEN MAX(TOTAL) ELSE MAX(Total)-9 END

FROM AddRN a

GROUP BY PERSON, [DATE], rn

HAVING rn IN (1, (SELECT COUNT(*) FROM AddRN));

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

zulfansari (2/19/2014)
Hi Dwaine,
You are amazing, how do you come with such codes? 🙂
When cumulative Double total is less than 9 then you are right hours stay as they are.
Can I ask you a dumb question?
If I already have data in a temp table, then how would I use your earlier supplied query without using the 'WITH SampleData (PERSON, [DATE], [HOURS], PAYCODE, TOTAL) AS' syntax, instead I want to use straight Select statement..
/** Here can I use something like this instead of using a WITH AS? **/
(SELECT * FROM PAYROLL_DETAIL
),
AddRN AS
(
SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY TOTAL)
FROM PAYROLL_DETAIL
WHERE TOTAL >= 9
)
SELECT PERSON, [DATE], [HOURS]=MAX([HOURS])
,PAYCODE=CASE rn WHEN 1 THEN MAX(PAYCODE) ELSE 'Triple' END
,TOTAL=CASE rn WHEN 1 THEN MAX(TOTAL) ELSE MAX(Total)-9 END
FROM AddRN a
GROUP BY PERSON, [DATE], rn
HAVING rn IN (1, (SELECT COUNT(*) FROM AddRN));

I believe you need to read up on Common Table Expressions. What I think you want to do is something like this (assuming your temp table is named #PAYROLL_DETAIL):

WITH AddRN AS

(

SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY TOTAL)

FROM #PAYROLL_DETAIL

WHERE TOTAL >= 9

)

SELECT PERSON, [DATE], [HOURS]=MAX([HOURS])

,PAYCODE=CASE rn WHEN 1 THEN MAX(PAYCODE) ELSE 'Triple' END

,TOTAL=CASE rn WHEN 1 THEN MAX(TOTAL) ELSE MAX(Total)-9 END

FROM AddRN a

GROUP BY PERSON, [DATE], rn

HAVING rn IN (1, (SELECT COUNT(*) FROM AddRN));

Thanks for saying I'm amazing. If only it were true!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 11

Hi Dwaine,

Once again appreciate your help and kindness.

I ran the query and added two new rows of data for employee (1125) and got weird results.

Also, I know you said the last employee is not going to show up because he has less than 9 hours of Double so for him can we still show the hours as they are worked?

-- Temp table has a clustered index to support the way

-- we'll calculate your running total.

CREATE TABLE #Temp

(

PERSON INT NOT NULL

,[DATE] DATE NOT NULL

,[HOURS] DECIMAL(5,2) NOT NULL

,CumHrs DECIMAL(5,2) NULL

,CONSTRAINT t_pk PRIMARY KEY(PERSON, [DATE])

);

-- The CTE represents what I think you've got in your Payroll Detail table.

-- I've added a couple of employees

WITH Payroll_Detail (PERSON, [DATE], [HOURS]) AS

(

-- First employee - running total > 9

SELECT 1125,CAST('2014-01-27' AS DATE),3.00

UNION ALL SELECT 1125,'2014-01-28',2.50

UNION ALL SELECT 1125,'2014-01-29',3.00

UNION ALL SELECT 1125,'2014-01-30',3.00

UNION ALL SELECT 1125,'2014-01-31',1.00

UNION ALL SELECT 1125,'2014-02-01',2.00

-- Second employee - running total = 9

UNION ALL SELECT 1126,'2014-01-27',2.00

UNION ALL SELECT 1126,'2014-01-28',2.50

UNION ALL SELECT 1126,'2014-01-29',3.00

UNION ALL SELECT 1126,'2014-01-30',1.50

-- I am not sure what to return for this guy, who's got less than

-- 9 hours. I return nothing but that could be changed.

UNION ALL SELECT 1127,'2014-01-27',2.00

UNION ALL SELECT 1127,'2014-01-28',2.50

UNION ALL SELECT 1127,'2014-01-29',3.00

UNION ALL SELECT 1127,'2014-01-30',1.00

)

-- Populate the temp table

INSERT INTO #Temp (PERSON, [DATE], [HOURS])

SELECT *

FROM Payroll_Detail;

-- Set up for the Quirky Update (QU) to calculate running totals

DECLARE @RunningTotal DECIMAL(5,2) = 0

,@Person INT = 0;

-- QU that calculates the running total. Query hints (WITH, OPTION) are important.

UPDATE #Temp WITH(TABLOCKX)

SET @RunningTotal = CumHrs = CASE PERSON WHEN @Person THEN @RunningTotal + [Hours] ELSE [Hours] END

,@Person = PERSON

OPTION (MAXDOP 1);

-- Now we retrieve what I think you want from the temp table

SELECT PERSON, [DATE], [HOURS]

,[PAYCODE]

,[TOTAL]

FROM

(

SELECT *

,rn=CASE WHEN CumHrs >= 9 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY [DATE]) END

FROM #Temp a

CROSS APPLY (VALUES('Double'),('Triple')) b([PAYCODE])

) a

CROSS APPLY

(

SELECT [TOTAL]=CASE WHEN [PAYCODE] = 'Double' AND CumHrs <= 9 THEN CumHrs

WHEN [PAYCODE] = 'Double' THEN 9

ELSE CumHrs - 9 END

) b

WHERE rn IS NULL AND [TOTAL] > 0;

GO

DROP TABLE #Temp

Current Results

PERSONDATE HOURSPAYCODE TOTAL

11252014-01-30 3.00Double 9.00

11252014-01-30 3.00Triple 2.50

11252014-01-31 1.00Double 9.00

11252014-01-31 1.00Triple 3.50

11252014-02-01 2.00Double 9.00

11252014-02-01 2.00Triple 5.50

11262014-01-30 1.50Double 9.00

The desired results

PERSONDATE HOURSPAYCODE TOTAL

11252014-01-27 3.00Double 3.00

11252014-01-28 2.50Double 2.50

11252014-01-29 3.00Double 3.00

11252014-01-30 3.00Double 0.50

11252014-01-30 3.00Triple 1.50

11252014-01-31 1.00Triple 1.00

11252014-02-01 2.00Triple 2.00

Dwain Camps SSC Guru Points: 86908 More actions · Answer 12

Well Z, as far as I can tell you've changed your requirements (again) so now I don't know whether you want a running total or just the hours split on the 9 hour boundary. That is a totally different query.

-- Temp table has a clustered index to support the way

-- we'll calculate your running total.

CREATE TABLE #Temp

(

PERSON INT NOT NULL

,[DATE] DATE NOT NULL

,[HOURS] DECIMAL(5,2) NOT NULL

,CumHrs DECIMAL(5,2) NULL

,CONSTRAINT t_pk PRIMARY KEY(PERSON, [DATE])

);

-- The CTE represents what I think you've got in your Payroll Detail table.

-- I've added a couple of employees

WITH Payroll_Detail (PERSON, [DATE], [HOURS]) AS

(

-- First employee - running total > 9

SELECT 1125,CAST('2014-01-27' AS DATE),3.00

UNION ALL SELECT 1125,'2014-01-28',2.50

UNION ALL SELECT 1125,'2014-01-29',3.00

UNION ALL SELECT 1125,'2014-01-30',3.00

UNION ALL SELECT 1125,'2014-01-31',1.00

UNION ALL SELECT 1125,'2014-02-01',2.00

-- Second employee - running total = 9

UNION ALL SELECT 1126,'2014-01-27',2.00

UNION ALL SELECT 1126,'2014-01-28',2.50

UNION ALL SELECT 1126,'2014-01-29',3.00

UNION ALL SELECT 1126,'2014-01-30',1.50

-- I am not sure what to return for this guy, who's got less than

-- 9 hours. I return nothing but that could be changed.

UNION ALL SELECT 1127,'2014-01-27',2.00

UNION ALL SELECT 1127,'2014-01-28',2.50

UNION ALL SELECT 1127,'2014-01-29',3.00

UNION ALL SELECT 1127,'2014-01-30',1.00

)

-- Populate the temp table

INSERT INTO #Temp (PERSON, [DATE], [HOURS])

SELECT *

FROM Payroll_Detail;

-- Set up for the Quirky Update (QU) to calculate running totals

DECLARE @RunningTotal DECIMAL(5,2) = 0

,@Person INT = 0;

-- QU that calculates the running total. Query hints (WITH, OPTION) are important.

UPDATE #Temp WITH(TABLOCKX)

SET @RunningTotal = CumHrs = CASE PERSON WHEN @Person THEN @RunningTotal + [Hours] ELSE [Hours] END

,@Person = PERSON

OPTION (MAXDOP 1);

--SELECT * FROM #Temp;

SELECT PERSON, [Date], [HOURS], rn, b.[PAYCODE]

,[TOTAL1]=CASE WHEN rn=1 AND b.[PAYCODE]='Triple' THEN a.[Total] ELSE b.[TOTAL] END

,[TOTAL2]=CASE WHEN rn=1 AND a.[PAYCODE]='Triple' AND b.[PAYCODE]='Double'

THEN b.[Total]

WHEN rn=1 AND a.[PAYCODE]='Triple'

THEN a.[Total]

ELSE [HOURS] END

,b.[TOTAL]

,CumHrs

FROM

(

SELECT PERSON, [Date], [HOURS], CumHrs

,rn=ROW_NUMBER() OVER (PARTITION BY PERSON, CASE WHEN CumHrs < 9 THEN 0 ELSE 1 END ORDER BY [DATE])

,[PAYCODE]=CASE WHEN CumHrs < 9 THEN 'Double' ELSE 'Triple' END

,[TOTAL]=CASE WHEN CumHrs < 9 THEN CumHrs ELSE CumHrs - 9 END

FROM #Temp a

) a

CROSS APPLY

(

VALUES (CASE WHEN rn=1 AND [PAYCODE]='Triple' THEN 'Double' END, 9-(CumHrs-[HOURS]))

,([PAYCODE], [TOTAL])

) b([PAYCODE], [TOTAL])

WHERE b.[PAYCODE] IS NOT NULL

ORDER BY PERSON, [DATE];

GO

DROP TABLE #Temp

Pick the TOTAL column you want from the one above.

However I need to caution you here. Please don't come back saying the query produces weird results again. If it not producing the results you want, start from the beginning. Provide sufficient test data to fully elucidate your required output. Be very careful when you show the required output that you do so for the full set of your sample data (e.g., in your above post you omitted what you want to see for 1126 and 1127).

Remember that we are all free helpers here who do so out of altruism and because it is fun for us. The minute it becomes not fun, we're likely to simply go dark.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 13

Hi Dwaine,

I would like to apologize and say that I didn't mean to say it the way it came out, I know you guys are here to help everyone and I also do know that all of you are extremely busy in your daily lives yet you still take the time to help people and I really appreciate it and am thankful for it.

I'm sorry once again for mixing up the requirements as I'm getting mixed information from the client, I guess I'm just over thinking the problem and the solution.

I have said it before with all sincerity that Hats off to you sir!!

I sincerely appreciate all the help you have given me.

Thank you,

Dwain Camps SSC Guru Points: 86908 More actions · Answer 14

zulfansari (2/20/2014)
Hi Dwaine,
I would like to apologize and say that I didn't mean to say it the way it came out, I know you guys are here to help everyone and I also do know that all of you are extremely busy in your daily lives yet you still take the time to help people and I really appreciate it and am thankful for it.
I'm sorry once again for mixing up the requirements as I'm getting mixed information from the client, I guess I'm just over thinking the problem and the solution.
I have said it before with all sincerity that Hats off to you sir!!
I sincerely appreciate all the help you have given me.
Thank you,

No apology necessary really but appreciated nonetheless. I understand when requirements change. Those pesky business users have a penchant for changing their minds.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 15

Hi Dwaine,

First of all your last script worked wonderfully so thank you again 🙂

Now, not changing the requirements and trust me I tried to follow your last script but it's a little too much to digest right away, I know I will get it eventually, how would I change (if I have to) the 9 hours requirement to 3 hours?

I did change the query from 9 to 3 but I know it's more than that 🙂

Thank you and sorry to keep bothering you, I promise this is the last question..