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 «««23456»»»

15 minutes Interval from Hours - Please Help Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 8:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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;

Post #1542832
Posted Tuesday, February 18, 2014 8:57 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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1542834
Posted Tuesday, February 18, 2014 9:22 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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1542837
Posted Tuesday, February 18, 2014 9:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542840
Posted Tuesday, February 18, 2014 9:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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;

Post #1542841
Posted Tuesday, February 18, 2014 9:37 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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1542843
Posted Tuesday, February 18, 2014 9:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Agree with both of you

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

Thank you again guys.
Post #1542853
Posted Tuesday, February 18, 2014 11: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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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. As the author, SQL MVP Jeff Moden likes to say it is "nasty fast."



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 #1542869
Posted Wednesday, February 19, 2014 12:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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));

Post #1542871
Posted Wednesday, February 19, 2014 1:08 AM


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: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1542881
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse