Post reply

15 minutes Interval from Hours - Please Help

  • February 17, 2014 at 8:27 pm

    #1689708

    Thank you SQLRNNR.

    By the way, what is FWIW? 🙂

  • February 17, 2014 at 8:30 pm

    #1689709

    FWIW = "For What it's Worth"

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 17, 2014 at 8:40 pm

    #1689713

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    --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)

  • February 17, 2014 at 9:44 pm

    #1689721

    Jeff Moden (2/17/2014)

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    Who is Red Green? Any relation to Opal Azure?

    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

  • February 17, 2014 at 10:16 pm

    #1689727

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    Who is Red Green? Any relation to Opal Azure?

    I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.

    http://en.wikipedia.org/wiki/The_Red_Green_Show

    --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)

  • February 17, 2014 at 10:36 pm

    #1689731

    Jeff Moden (2/17/2014)

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    Who is Red Green? Any relation to Opal Azure?

    I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.

    http://en.wikipedia.org/wiki/The_Red_Green_Show

    Looks like the Canuck version of Hee Haw

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/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

  • February 18, 2014 at 11:12 am

    #1690006

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    Who is Red Green? Any relation to Opal Azure?

    I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.

    http://en.wikipedia.org/wiki/The_Red_Green_Show

    Looks like the Canuck version of The Bachelor

    There, fixed that for you. And I didn't even use duct tape!

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

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • February 18, 2014 at 4:14 pm

    #1690093

    Hi Guys,

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

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

    SELECT PERSON,CONVERT(VARCHAR(10), DATE,101) DATE,HOURS = t.N*.25,PAYCODE

    FROM Payroll_Detail_OT

    CROSS JOIN dbo.Tally t

    WHERE t.N <= HOURS*4

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

    PERSONDATE HOURSPAYCODE TOTAL

    112527/01/20140.25Double 0.25

    112527/01/20140.50Double 0.50

    112527/01/20140.75Double 0.75

    112527/01/20141.00Double 1.00

    112527/01/20141.25Double 1.25

    112527/01/20141.50Double 1.50

    112527/01/20141.75Double 1.75

    112527/01/20142.00Double 2.00

    112527/01/20142.25Double 2.25

    112527/01/20142.50Double 2.50

    112527/01/20142.75Double 2.75

    112527/01/20143.00Double 3.00

    112528/01/20140.25Double 3.25

    112528/01/20140.50Double 3.50

    112528/01/20140.75Double 3.75

    112528/01/20141.00Double 4.00

    112528/01/20141.25Double 4.25

    112528/01/20141.50Double 4.50

    112528/01/20141.75Double 4.75

    112528/01/20142.00Double 5.00

    112528/01/20142.25Double 5.25

    112528/01/20142.50Double 5.50

    112529/01/20140.25Double 5.75

    112529/01/20140.50Double 6.00

    112529/01/20140.75Double 6.25

    112529/01/20141.00Double 6.50

    112529/01/20141.25Double 6.75

    112529/01/20141.50Double 7.00

    112529/01/20141.75Double 7.25

    112529/01/20142.00Double 7.50

    112529/01/20142.25Double 7.75

    112529/01/20142.50Double 8.00

    112529/01/20142.75Double 8.20

    112529/01/20143.00Double 8.50

    112530/01/20140.25Double 8.75

    112530/01/20140.50Double 9.00

    112530/01/20140.75Double 9.25

    112530/01/20141.00Double 9.50

    112530/01/20141.25Double 9.75

    112530/01/20141.50Double 10.00

    112530/01/20141.75Double 10.25

    112530/01/20142.00Double 10.50

    112530/01/20142.25Double 10.75

    112530/01/20142.50Double 11.00

    112530/01/20142.75Double 11.25

    112530/01/20143.00Double 11.50

    So the final query should give the following result:

    PERSONDATE HOURSPAYCODE TOTAL

    112530/01/20140.25Double 9.00

    112530/01/20140.50Triple 2.50

  • February 18, 2014 at 5:12 pm

    #1690104

    jcrawf02 (2/18/2014)

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    dwain.c (2/17/2014)

    Jeff Moden (2/17/2014)

    zulfansari (2/17/2014)

    Hi Jeff, Dwain.c and SQLRNNR,

    You guys are AWESOME..

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

    God bless you all!

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

    Best Regards,

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

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

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

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

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

    Who is Red Green? Any relation to Opal Azure?

    I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.

    http://en.wikipedia.org/wiki/The_Red_Green_Show

    Looks like the Canuck version of The Bachelor

    There, fixed that for you. And I didn't even use duct tape!

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

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/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

  • February 18, 2014 at 5:32 pm

    #1690109

    zulfansari (2/18/2014)

    Hi Guys,

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

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

    SELECT PERSON,CONVERT(VARCHAR(10), DATE,101) DATE,HOURS = t.N*.25,PAYCODE

    FROM Payroll_Detail_OT

    CROSS JOIN dbo.Tally t

    WHERE t.N <= HOURS*4

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

    PERSONDATE HOURSPAYCODE TOTAL

    112527/01/20140.25Double 0.25

    112527/01/20140.50Double 0.50

    112527/01/20140.75Double 0.75

    112527/01/20141.00Double 1.00

    112527/01/20141.25Double 1.25

    112527/01/20141.50Double 1.50

    112527/01/20141.75Double 1.75

    112527/01/20142.00Double 2.00

    112527/01/20142.25Double 2.25

    112527/01/20142.50Double 2.50

    112527/01/20142.75Double 2.75

    112527/01/20143.00Double 3.00

    112528/01/20140.25Double 3.25

    112528/01/20140.50Double 3.50

    112528/01/20140.75Double 3.75

    112528/01/20141.00Double 4.00

    112528/01/20141.25Double 4.25

    112528/01/20141.50Double 4.50

    112528/01/20141.75Double 4.75

    112528/01/20142.00Double 5.00

    112528/01/20142.25Double 5.25

    112528/01/20142.50Double 5.50

    112529/01/20140.25Double 5.75

    112529/01/20140.50Double 6.00

    112529/01/20140.75Double 6.25

    112529/01/20141.00Double 6.50

    112529/01/20141.25Double 6.75

    112529/01/20141.50Double 7.00

    112529/01/20141.75Double 7.25

    112529/01/20142.00Double 7.50

    112529/01/20142.25Double 7.75

    112529/01/20142.50Double 8.00

    112529/01/20142.75Double 8.20

    112529/01/20143.00Double 8.50

    112530/01/20140.25Double 8.75

    112530/01/20140.50Double 9.00

    112530/01/20140.75Double 9.25

    112530/01/20141.00Double 9.50

    112530/01/20141.25Double 9.75

    112530/01/20141.50Double 10.00

    112530/01/20141.75Double 10.25

    112530/01/20142.00Double 10.50

    112530/01/20142.25Double 10.75

    112530/01/20142.50Double 11.00

    112530/01/20142.75Double 11.25

    112530/01/20143.00Double 11.50

    So the final query should give the following result:

    PERSONDATE HOURSPAYCODE TOTAL

    112530/01/20140.25Double 9.00

    112530/01/20140.50Triple 2.50

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

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

    (

    SELECT 1125,'30/01/2014',0.25,'Double',8.75

    UNION ALL SELECT 1125,'2014-01-30',0.50,'Double',9.00

    UNION ALL SELECT 1125,'2014-01-30',0.75,'Double',9.25

    UNION ALL SELECT 1125,'2014-01-30',1.00,'Double',9.50

    UNION ALL SELECT 1125,'2014-01-30',1.25,'Double',9.75

    UNION ALL SELECT 1125,'2014-01-30',1.50,'Double',10.00

    UNION ALL SELECT 1125,'2014-01-30',1.75,'Double',10.25

    UNION ALL SELECT 1125,'2014-01-30',2.00,'Double',10.50

    UNION ALL SELECT 1125,'2014-01-30',2.25,'Double',10.75

    UNION ALL SELECT 1125,'2014-01-30',2.50,'Double',11.00

    UNION ALL SELECT 1125,'2014-01-30',2.75,'Double',11.25

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

    )

    SELECT *

    FROM SampleData;

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

    PERSON DATE HOURS PAYCODE TOTAL

    1125 30/01/2014 0.25 Double 9.00

    1125 30/01/2014 0.50 Triple 2.50

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

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/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

  • February 18, 2014 at 7:11 pm

    #1690115

    dwain.c (2/18/2014)

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

    PERSON DATE HOURS PAYCODE TOTAL

    1125 30/01/2014 0.25 Double 9.00

    1125 30/01/2014 0.50 Triple 2.50

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

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

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 18, 2014 at 7:45 pm

    #1690121

    Hi Dwain,

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

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

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

    Thank you very much for your help..

    PERSON DATE HOURS PAYCODE TOTAL

    1125 30/01/2014 0.50 Double 9.00

    1125 30/01/2014 0.75 Triple 2.50

  • February 18, 2014 at 8:10 pm

    #1690122

    Sorry I forgot to add the sample data.

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

    (

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    )

    SELECT *

    FROM SampleData;

  • February 18, 2014 at 8:25 pm

    #1690123

    zulfansari (2/18/2014)

    Hi Dwain,

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

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

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

    Thank you very much for your help..

    PERSON DATE HOURS PAYCODE TOTAL

    1125 30/01/2014 0.50 Double 9.00

    1125 30/01/2014 0.75 Triple 2.50

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

    - Are you expecting only 2 rows in your final output? If you expect anything other than 2 rows, please show all as your expected results.

    - Where does the 2.5 value come from in the TOTAL column for Triple? I know you said it is the remainder, but I can't see the remainder of what. Which specific rows is the 2.5 calculated from?

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/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

  • February 18, 2014 at 8:44 pm

    #1690126

    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

Viewing 15 posts - 16 through 30 (of 53 total)

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