15 minutes Interval from Hours - Please Help

  • Hello SQL Gurus,

    I have a table with three columns, Person, Date, Hours. I need to turn hours into 15 minutes interval.

    Can someone please help?

    Thank you,

    Here is what I have

    Person Date Hours

    101 02/01/2014 1.00

    101 02/02/2014 1.30

    It should be like

    Person Date Hours

    101 02/01/2014 0.15

    101 02/01/2014 0.30

    101 02/01/2014 0.45

    101 02/01/2014 1.00

    101 02/02/2014 0.15

    101 02/02/2014 0.30

    101 02/02/2014 0.45

    101 02/02/2014 1.00

    101 02/02/2014 1.15

    101 02/02/2014 1.30

  • Here is something that will work. One significant difference is that when converting time to decimal, you usually use .25 instead of 15 increments. Based on that, here is the script.

    DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))

    DECLARE @somenums TABLE (Numbers DECIMAL(12,2))

    INSERT INTO @sometab

    ( Person, Date, Hours )

    VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);

    INSERT INTO @somenums

    ( Numbers )

    SELECT (N *.25)

    FROM AdminDB.dbo.Numbers

    WHERE N < 100;

    SELECT t.Person,t.Date,s.Numbers AS Hours

    FROM @sometab t

    CROSS APPLY @somenums s

    WHERE t.[Hours] >= s.Numbers

    ORDER BY t.[Date],s.Numbers

    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

  • If you don't have a numbers table at your disposal like Jason does, this will also work.

    DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))

    INSERT INTO @sometab

    ( Person, Date, Hours )

    VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);

    WITH Tally (n) AS

    (

    SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))

    FROM @sometab a

    CROSS APPLY

    (

    SELECT n

    FROM Tally

    WHERE n <= 4.*[Hours]

    ) b

    ORDER BY Person, [Date], n;


    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

  • Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..

  • dwain.c (2/16/2014)


    If you don't have a numbers table at your disposal like Jason does, this will also work.

    DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))

    INSERT INTO @sometab

    ( Person, Date, Hours )

    VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);

    WITH Tally (n) AS

    (

    SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))

    FROM @sometab a

    CROSS APPLY

    (

    SELECT n

    FROM Tally

    WHERE n <= 4.*[Hours]

    ) b

    ORDER BY Person, [Date], n;

    Shhh... I was waiting for somebody to ask about that 😉

    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

  • SQLRNNR (2/16/2014)


    dwain.c (2/16/2014)


    If you don't have a numbers table at your disposal like Jason does, this will also work.

    DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))

    INSERT INTO @sometab

    ( Person, Date, Hours )

    VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);

    WITH Tally (n) AS

    (

    SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))

    FROM @sometab a

    CROSS APPLY

    (

    SELECT n

    FROM Tally

    WHERE n <= 4.*[Hours]

    ) b

    ORDER BY Person, [Date], n;

    Shhh... I was waiting for somebody to ask about that 😉

    Since the secret is out ;-), please see the following article for what a Tally Table or similar structure is an how it can be used to replace certain loops in a very high performance manner. It'll change the way you think in T-SQL.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

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

  • zulfansari (2/16/2014)


    Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..

    OK I'll bite. What's a Numbers table?


    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.c (2/16/2014)


    zulfansari (2/16/2014)


    Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..

    OK I'll bite. What's a Numbers table?

    Jeff gave up that link already too 😀

    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

  • zulfansari (2/14/2014)


    Hello SQL Gurus,

    I have a table with three columns, Person, Date, Hours. I need to turn hours into 15 minutes interval.

    Can someone please help?

    Thank you,

    Here is what I have

    Person Date Hours

    101 02/01/2014 1.00

    101 02/02/2014 1.30

    It should be like

    Person Date Hours

    101 02/01/2014 0.15

    101 02/01/2014 0.30

    101 02/01/2014 0.45

    101 02/01/2014 1.00

    101 02/02/2014 0.15

    101 02/02/2014 0.30

    101 02/02/2014 0.45

    101 02/02/2014 1.00

    101 02/02/2014 1.15

    101 02/02/2014 1.30

    There's a bit of confusion in your original data. Are the decimal places minutes or decimal hours? You seem to indicate in your desired output that they're hours but I wanted to make sure.

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

  • Hi Jeff,

    Actually it's 0.25,0.50, etc. format, I made up the data for the post and used the wrong format.

    Thank you,

  • Hello Dwayne,

    I tried the script but it just keeps on running.

    Here is the actual script:

    WITH Tally (n) AS

    (

    SELECT TOP ((SELECT 1+CAST(4*MAX([hours]) AS INT) FROM TOTALS

    where PERSON = '1010' and DATE between '02/05/2014' and '02/11/2014'

    ))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT PERSON, DATE, [hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))

    FROM TOTALS a

    CROSS APPLY

    (

    SELECT n

    FROM Tally

    WHERE n <= 4.*[hours]

    ) b

    ORDER BY PERSON, [DATE], n;

  • You might try putting the WHERE clause before ORDER BY instead of where you have it (the subquery that determines the TOP n rows in your Tally table).


    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

  • zulfansari (2/16/2014)


    Hi Jeff,

    Actually it's 0.25,0.50, etc. format, I made up the data for the post and used the wrong format.

    Thank you,

    Then a Tally Table will make the solution to this problem incredibly easy. I left the "Hours" mix in the output just so you can verify the output. You can remove that column whenever you're ready.

    --===== Build a test table and populate it on-the-fly.

    -- THIS IS NOT A PART OF THE SOLUTION!!!

    -- WE JUST NEEDED SOMETHING TO DEMO THE SOLUTION WITH!!!

    SET DATEFORMAT MDY;

    SELECT d.Person

    ,[Date] = CAST(d.[Date] AS DATETIME)

    ,d.Hours

    INTO #TestTable

    FROM (

    SELECT 101,'02/01/2014', 1.00 UNION ALL

    SELECT 101,'02/02/2014', 1.25 UNION ALL

    SELECT 101,'02/03/2014',11.75

    )d(Person,[Date],Hours)

    ;

    --===== This is the solution made incredibly easy

    -- by the use of a Tally Table.

    SELECT Person,[Date],Hours,IntervalHours = t.N*.25

    FROM #TestTable

    CROSS JOIN dbo.Tally t

    WHERE t.N <= Hours*4

    ORDER BY Person, Date, IntervalHours

    ;

    Here's the output from the code above. Like I said, you can simply remove the "Hours" column if you don't really want it.

    Person Date Hours IntervalHours

    ----------- ----------------------- ----- -------------

    101 2014-02-01 00:00:00.000 1.00 0.25

    101 2014-02-01 00:00:00.000 1.00 0.50

    101 2014-02-01 00:00:00.000 1.00 0.75

    101 2014-02-01 00:00:00.000 1.00 1.00

    101 2014-02-02 00:00:00.000 1.25 0.25

    101 2014-02-02 00:00:00.000 1.25 0.50

    101 2014-02-02 00:00:00.000 1.25 0.75

    101 2014-02-02 00:00:00.000 1.25 1.00

    101 2014-02-02 00:00:00.000 1.25 1.25

    101 2014-02-03 00:00:00.000 11.75 0.25

    101 2014-02-03 00:00:00.000 11.75 0.50

    101 2014-02-03 00:00:00.000 11.75 0.75

    101 2014-02-03 00:00:00.000 11.75 1.00

    101 2014-02-03 00:00:00.000 11.75 1.25

    101 2014-02-03 00:00:00.000 11.75 1.50

    101 2014-02-03 00:00:00.000 11.75 1.75

    101 2014-02-03 00:00:00.000 11.75 2.00

    101 2014-02-03 00:00:00.000 11.75 2.25

    101 2014-02-03 00:00:00.000 11.75 2.50

    101 2014-02-03 00:00:00.000 11.75 2.75

    101 2014-02-03 00:00:00.000 11.75 3.00

    101 2014-02-03 00:00:00.000 11.75 3.25

    101 2014-02-03 00:00:00.000 11.75 3.50

    101 2014-02-03 00:00:00.000 11.75 3.75

    101 2014-02-03 00:00:00.000 11.75 4.00

    101 2014-02-03 00:00:00.000 11.75 4.25

    101 2014-02-03 00:00:00.000 11.75 4.50

    101 2014-02-03 00:00:00.000 11.75 4.75

    101 2014-02-03 00:00:00.000 11.75 5.00

    101 2014-02-03 00:00:00.000 11.75 5.25

    101 2014-02-03 00:00:00.000 11.75 5.50

    101 2014-02-03 00:00:00.000 11.75 5.75

    101 2014-02-03 00:00:00.000 11.75 6.00

    101 2014-02-03 00:00:00.000 11.75 6.25

    101 2014-02-03 00:00:00.000 11.75 6.50

    101 2014-02-03 00:00:00.000 11.75 6.75

    101 2014-02-03 00:00:00.000 11.75 7.00

    101 2014-02-03 00:00:00.000 11.75 7.25

    101 2014-02-03 00:00:00.000 11.75 7.50

    101 2014-02-03 00:00:00.000 11.75 7.75

    101 2014-02-03 00:00:00.000 11.75 8.00

    101 2014-02-03 00:00:00.000 11.75 8.25

    101 2014-02-03 00:00:00.000 11.75 8.50

    101 2014-02-03 00:00:00.000 11.75 8.75

    101 2014-02-03 00:00:00.000 11.75 9.00

    101 2014-02-03 00:00:00.000 11.75 9.25

    101 2014-02-03 00:00:00.000 11.75 9.50

    101 2014-02-03 00:00:00.000 11.75 9.75

    101 2014-02-03 00:00:00.000 11.75 10.00

    101 2014-02-03 00:00:00.000 11.75 10.25

    101 2014-02-03 00:00:00.000 11.75 10.50

    101 2014-02-03 00:00:00.000 11.75 10.75

    101 2014-02-03 00:00:00.000 11.75 11.00

    101 2014-02-03 00:00:00.000 11.75 11.25

    101 2014-02-03 00:00:00.000 11.75 11.50

    101 2014-02-03 00:00:00.000 11.75 11.75

    As previously posted, here's where you can learn more about the Tally Table, how to build it, and how it works to avoid loops and cursors.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    Once you've mastered using a physical Tally Table, then you can get into what Jason and Dwain where showing... on the fly creation of Tally-Table-like structures, which is also introduced in the article I provided a link to.

    The reason why I don't just cough up the very simple method of building a Tally Table is because I really want you to understand how the Tally Table works. My personal belief is that it will improve your career as someone using T-SQL as it has done for nearly everyone that's ever used such a thing. It changes your mind from thinking in rows to thinking in columns and that's the main paradigm shift required to write some really nasty fast code in T-SQL.

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

  • 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,

  • 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,

    Good to hear. FWIW, the Numbers table in the script I shared and the Tally table in Jeff's script are the same thing - just different names.

    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

Viewing 15 posts - 1 through 15 (of 53 total)

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