Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

Author
 Message
 Posted Tuesday, February 18, 2014 8:55 PM
 Valued 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 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 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 ENDFROM AddRN aGROUP BY PERSON, [DATE], rnHAVING 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 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 PAYCODE1125 27/01/2014 0.25 Double 1125 27/01/2014 0.50 Double1125 27/01/2014 0.75 Double 1125 27/01/2014 1.00 Double1125 27/01/2014 1.25 Double 1125 27/01/2014 1.50 Double1125 27/01/2014 1.75 Double 1125 27/01/2014 2.00 Double1125 27/01/2014 2.25 Double 1125 27/01/2014 2.50 Double1125 27/01/2014 2.75 Double 1125 27/01/2014 3.00 Double1125 28/01/2014 0.25 Double 1125 28/01/2014 0.50 Double1125 28/01/2014 0.75 Double 1125 28/01/2014 1.00 Double1125 28/01/2014 1.25 Double 1125 28/01/2014 1.50 Double1125 28/01/2014 1.75 Double 1125 28/01/2014 2.00 Double1125 28/01/2014 2.25 Double 1125 28/01/2014 2.50 Double`Section B - Desired Result`PERSON DATE HOURS PAYCODE TOTAL1125 27/01/2014 0.25 Double 0.25 1125 27/01/2014 0.50 Double 0.501125 27/01/2014 0.75 Double 0.75 1125 27/01/2014 1.00 Double 1.001125 27/01/2014 1.25 Double 1.25 1125 27/01/2014 1.50 Double 1.501125 27/01/2014 1.75 Double 1.75 1125 27/01/2014 2.00 Double 2.001125 27/01/2014 2.25 Double 2.251125 27/01/2014 2.50 Double 2.501125 27/01/2014 2.75 Double 2.75 1125 27/01/2014 3.00 Double 3.001125 28/01/2014 0.25 Double 3.25 1125 28/01/2014 0.50 Double 3.501125 28/01/2014 0.75 Double 3.75 1125 28/01/2014 1.00 Double 4.001125 28/01/2014 1.25 Double 4.25 1125 28/01/2014 1.50 Double 4.501125 28/01/2014 1.75 Double 4.75 1125 28/01/2014 2.00 Double 5.001125 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 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1542840
 Posted Tuesday, February 18, 2014 9:37 PM
 Valued 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 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 PAYCODE1125 27/01/2014 0.25 Double 1125 27/01/2014 0.50 Double1125 27/01/2014 0.75 Double 1125 27/01/2014 1.00 Double1125 27/01/2014 1.25 Double 1125 27/01/2014 1.50 Double1125 27/01/2014 1.75 Double 1125 27/01/2014 2.00 Double1125 27/01/2014 2.25 Double 1125 27/01/2014 2.50 Double1125 27/01/2014 2.75 Double 1125 27/01/2014 3.00 Double1125 28/01/2014 0.25 Double 1125 28/01/2014 0.50 Double1125 28/01/2014 0.75 Double 1125 28/01/2014 1.00 Double1125 28/01/2014 1.25 Double 1125 28/01/2014 1.50 Double1125 28/01/2014 1.75 Double 1125 28/01/2014 2.00 Double1125 28/01/2014 2.25 Double 1125 28/01/2014 2.50 Double`Section B - Desired Result`PERSON DATE HOURS PAYCODE TOTAL1125 27/01/2014 0.25 Double 0.25 1125 27/01/2014 0.50 Double 0.501125 27/01/2014 0.75 Double 0.75 1125 27/01/2014 1.00 Double 1.001125 27/01/2014 1.25 Double 1.25 1125 27/01/2014 1.50 Double 1.501125 27/01/2014 1.75 Double 1.75 1125 27/01/2014 2.00 Double 2.001125 27/01/2014 2.25 Double 2.251125 27/01/2014 2.50 Double 2.501125 27/01/2014 2.75 Double 2.75 1125 27/01/2014 3.00 Double 3.001125 28/01/2014 0.25 Double 3.25 1125 28/01/2014 0.50 Double 3.501125 28/01/2014 0.75 Double 3.75 1125 28/01/2014 1.00 Double 4.001125 28/01/2014 1.25 Double 4.25 1125 28/01/2014 1.50 Double 4.501125 28/01/2014 1.75 Double 4.75 1125 28/01/2014 2.00 Double 5.001125 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 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 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 employeesWITH 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 tableINSERT INTO #Temp (PERSON, [DATE], [HOURS])SELECT *FROM Payroll_Detail;-- Set up for the Quirky Update (QU) to calculate running totalsDECLARE @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 = PERSONOPTION (MAXDOP 1);-- Now we retrieve what I think you want from the temp tableSELECT 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])) aCROSS APPLY( SELECT [TOTAL]=CASE WHEN [PAYCODE] = 'Double' AND CumHrs <= 9 THEN CumHrs WHEN [PAYCODE] = 'Double' THEN 9 ELSE CumHrs - 9 END) bWHERE rn IS NULL AND [TOTAL] > 0;GODROP 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 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 ENDFROM AddRN aGROUP BY PERSON, [DATE], rnHAVING rn IN (1, (SELECT COUNT(*) FROM AddRN));`
Post #1542871
 Posted Wednesday, February 19, 2014 1:08 AM
 Hall 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 ENDFROM AddRN aGROUP BY PERSON, [DATE], rnHAVING 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 ENDFROM AddRN aGROUP BY PERSON, [DATE], rnHAVING 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

 Permissions