Filling gaps in date ranges

  • Hello,

    See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year. Thank you

    SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL

    SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL

    SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL

    SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2013-12-10 14:00:00.000' as dt, 22.50 as amt UNION ALL

    SELECT '2013-12-10 20:00:00.000' as dt, 241.50 as amt UNION ALL

    SELECT '2014-12-10 06:00:00.000' as dt, 667.50 as amt UNION ALL

    SELECT '2014-12-10 08:00:00.000' as dt, 444.50 as amt UNION ALL

    SELECT '2014-12-10 09:00:00.000' as dt, 67.50 as amt UNION ALL

    SELECT '2014-12-10 11:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2014-12-10 12:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2014-12-10 17:00:00.000' as dt, 82.50 as amt UNION ALL

    SELECT '2014-12-10 22:00:00.000' as dt, 221.50 as amt

    -- expected result

    SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL

    SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL

    SELECT '2013-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 06:00:00.000', 24.50 AS Amt UNION ALL

    SELECT '2013-12-10 07:00:00.000', 527.50 AS Amt UNION ALL

    SELECT '2013-12-10 08:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2013-12-10 09:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 11:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2013-12-10 12:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 14:00:00.000', 22.50 AS Amt UNION ALL

    SELECT '2013-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 17:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT '2013-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 22:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 23:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 00:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 04:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT '2014-12-10 07:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT '2014-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 11:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2014-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 14:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT '2014-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 20:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 22:00:00.000', 221.50 AS Amt UNION ALL

    SELECT '2014-12-10 23:00:00.000', NULL

  • You need to use a numbers or tally table as the main table in your query. Check out this article on the topic. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This should give you the general idea:

    WITH

    L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows

    L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows

    L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows

    L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows

    L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows

    --SEEN THIS BEFORE?? Order, but I don't care, so no actual SORT!

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L4)

    SELECT TOP (50) DATEADD(hh, N, '20131210') as dt, t2.amt

    FROM Nums

    left join (SELECT * FROM

    (SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL

    SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL

    SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL

    SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2013-12-10 14:00:00.000' as dt, 22.50 as amt UNION ALL

    SELECT '2013-12-10 20:00:00.000' as dt, 241.50 as amt UNION ALL

    SELECT '2014-12-10 06:00:00.000' as dt, 667.50 as amt UNION ALL

    SELECT '2014-12-10 08:00:00.000' as dt, 444.50 as amt UNION ALL

    SELECT '2014-12-10 09:00:00.000' as dt, 67.50 as amt UNION ALL

    SELECT '2014-12-10 11:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2014-12-10 12:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2014-12-10 17:00:00.000' as dt, 82.50 as amt UNION ALL

    SELECT '2014-12-10 22:00:00.000' as dt, 221.50 as amt) as t1) as t2 on t2.dt = DATEADD(hh, N, '20131210')

    ORDER BY N

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • clayman (12/10/2014)


    Hello,

    See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year. Thank you

    SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL

    SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL

    SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL

    SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2013-12-10 14:00:00.000' as dt, 22.50 as amt UNION ALL

    SELECT '2013-12-10 20:00:00.000' as dt, 241.50 as amt UNION ALL

    SELECT '2014-12-10 06:00:00.000' as dt, 667.50 as amt UNION ALL

    SELECT '2014-12-10 08:00:00.000' as dt, 444.50 as amt UNION ALL

    SELECT '2014-12-10 09:00:00.000' as dt, 67.50 as amt UNION ALL

    SELECT '2014-12-10 11:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2014-12-10 12:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2014-12-10 17:00:00.000' as dt, 82.50 as amt UNION ALL

    SELECT '2014-12-10 22:00:00.000' as dt, 221.50 as amt

    -- expected result

    SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL

    SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL

    SELECT '2013-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 06:00:00.000', 24.50 AS Amt UNION ALL

    SELECT '2013-12-10 07:00:00.000', 527.50 AS Amt UNION ALL

    SELECT '2013-12-10 08:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2013-12-10 09:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 11:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2013-12-10 12:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 14:00:00.000', 22.50 AS Amt UNION ALL

    SELECT '2013-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 17:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT '2013-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 22:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 23:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 00:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 04:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT '2014-12-10 07:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT '2014-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 11:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2014-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 14:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT '2014-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 20:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 22:00:00.000', 221.50 AS Amt UNION ALL

    SELECT '2014-12-10 23:00:00.000', NULL

    You've asked very nearly (as far as I can tell) the same question on a previous post and it was answered. Why wasn't that previous answer acceptable?

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

  • Jeff Moden (12/10/2014)


    clayman (12/10/2014)


    Hello,

    See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year. Thank you

    SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL

    SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL

    SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL

    SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2013-12-10 14:00:00.000' as dt, 22.50 as amt UNION ALL

    SELECT '2013-12-10 20:00:00.000' as dt, 241.50 as amt UNION ALL

    SELECT '2014-12-10 06:00:00.000' as dt, 667.50 as amt UNION ALL

    SELECT '2014-12-10 08:00:00.000' as dt, 444.50 as amt UNION ALL

    SELECT '2014-12-10 09:00:00.000' as dt, 67.50 as amt UNION ALL

    SELECT '2014-12-10 11:00:00.000' as dt, 28.50 as amt UNION ALL

    SELECT '2014-12-10 12:00:00.000' as dt, 25.50 as amt UNION ALL

    SELECT '2014-12-10 17:00:00.000' as dt, 82.50 as amt UNION ALL

    SELECT '2014-12-10 22:00:00.000' as dt, 221.50 as amt

    -- expected result

    SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL

    SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL

    SELECT '2013-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 06:00:00.000', 24.50 AS Amt UNION ALL

    SELECT '2013-12-10 07:00:00.000', 527.50 AS Amt UNION ALL

    SELECT '2013-12-10 08:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2013-12-10 09:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 11:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2013-12-10 12:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 14:00:00.000', 22.50 AS Amt UNION ALL

    SELECT '2013-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 17:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT '2013-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 22:00:00.000', NULL AS Amt UNION ALL

    SELECT '2013-12-10 23:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 00:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 04:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT '2014-12-10 07:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT '2014-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 11:00:00.000', 28.50 AS Amt UNION ALL

    SELECT '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT '2014-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 14:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT '2014-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 20:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT '2014-12-10 22:00:00.000', 221.50 AS Amt UNION ALL

    SELECT '2014-12-10 23:00:00.000', NULL

    You've asked very nearly (as far as I can tell) the same question on a previous post and it was answered. Why wasn't that previous answer acceptable?

    I'm currently reading you article. Thank you for your help!

  • Ah... sorry. I see what the difference is now.

    Kevin's answer uses Itzik Ben-Gan's "inline" version of a "Tally Table". It' sometimes a bit slower (and I do mean just a bit) than a physical Tally Table but it produces no reads and has no practical limit. It's the same concept though... it uses the natural "looping power" of SELECT to replace the action of a WHILE loop in a very high performance fashion.

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

  • Thanks again, appreciate the help. If I added an additional attribute (ProductId) in my sample data would it be possible to include this in the result set so that it returns the hourly amounts for each product for a given day for each year? Like this:

    Many thanks.

    Sample:

    SELECT 1 AS ProductId, '2013-12-10 04:00:00.000' AS dt, 220.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 06:00:00.000', 24.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 07:00:00.000', 527.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 08:00:00.000', 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 11:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 14:00:00.000', 22.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 11:00:00.000', 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 22:00:00.000', 221.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 04:00:00.000', 20.1 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 06:00:00.000', 24.20 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 07:00:00.000', 52.40 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 08:00:00.000', 8.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 11:00:00.000', 5.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 14:00:00.000', 2.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 16:00:00.000', 5.77 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 11:00:00.000', 255.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 14:00:00.000', 44 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 20:00:00.000', 55 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 22:00:00.000', 231.50 AS Amt

    Expected:

    SELECT 1 AS ProductId, '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 06:00:00.000', 24.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 07:00:00.000', 527.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 08:00:00.000', 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 09:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 11:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 12:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 14:00:00.000', 22.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 17:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 22:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 23:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 00:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 04:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 07:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 11:00:00.000', 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 14:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 20:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 22:00:00.000', 221.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 23:00:00.000', NULL UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 04:00:00.000', 20.1 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 06:00:00.000', 24.20 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 07:00:00.000', 52.40 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 08:00:00.000', 8.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 09:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 11:00:00.000', 5.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 12:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 14:00:00.000', 2.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 16:00:00.000', 5.77 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 17:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 20:00:00.000', 241.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 22:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 23:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 00:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 01:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 02:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 03:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 04:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 05:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 06:00:00.000', 667.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 07:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 08:00:00.000', 444.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 09:00:00.000', 67.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 10:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 11:00:00.000', 255.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 12:00:00.000', 25.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 13:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 14:00:00.000', 44 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 15:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 16:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 17:00:00.000', 82.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 18:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 19:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 20:00:00.000', 55 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 21:00:00.000', NULL AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 22:00:00.000', 231.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 23:00:00.000', NULL

  • I could be wrong, but if you want all possible combinations of ProductID and each date/time value from the existing query (well, what others have helped build), you would create a deliberate cartesian product between the Product table and the existing query. In other words, you'd add the Product table to the existing query, but not join it to anything. Net result is that you get all possible combinations of (ProductID, TimeSlot). Is that what you intended?

  • Quick solution based on an inline tally driven calendar table, should get you passed this hurdle

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @DATE_FROM DATETIME = '2013-12-10 04:00:00.000';

    DECLARE @DATE_TO DATETIME = '2014-12-10 22:00:00.000';

    ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS

    ( SELECT TOP (1 + (DATEDIFF(HOUR,@DATE_FROM,@DATE_TO))) ROW_NUMBER()

    OVER (ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6

    )

    ,PRODUCT_LOG(ProductId,dt,Amt) AS

    (

    SELECT 1 AS ProductId, '2013-12-10 04:00:00.000' AS dt, 220.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 06:00:00.000' , 24.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 07:00:00.000' , 527.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 08:00:00.000' , 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 11:00:00.000' , 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 14:00:00.000' , 22.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2013-12-10 20:00:00.000' , 241.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 06:00:00.000' , 667.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 08:00:00.000' , 444.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 09:00:00.000' , 67.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 11:00:00.000' , 28.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 12:00:00.000' , 25.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 17:00:00.000' , 82.50 AS Amt UNION ALL

    SELECT 1 AS ProductId, '2014-12-10 22:00:00.000' , 221.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 04:00:00.000' , 20.1 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 06:00:00.000' , 24.20 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 07:00:00.000' , 52.40 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 08:00:00.000' , 8.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 11:00:00.000' , 5.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 14:00:00.000' , 2.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 16:00:00.000' , 5.77 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2013-12-10 20:00:00.000' , 241.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 06:00:00.000' , 667.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 08:00:00.000' , 444.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 09:00:00.000' , 67.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 11:00:00.000' , 255.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 12:00:00.000' , 25.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 14:00:00.000' , 44 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 17:00:00.000' , 82.50 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 20:00:00.000' , 55 AS Amt UNION ALL

    SELECT 2 AS ProductId, '2014-12-10 22:00:00.000' , 231.50 AS Amt

    )

    ,PRODUCT_DATES(PR_DATE) AS

    (

    SELECT DISTINCT

    CONVERT(DATE,PL.dt,0) AS PR_DATE

    FROM PRODUCT_LOG PL

    )

    ,PRODUCT_LIST(ProductId) AS

    (

    SELECT DISTINCT

    PL.ProductId

    FROM PRODUCT_LOG PL

    )

    ,CALENDAR_TABLE AS

    (

    SELECT

    NM.N AS DH_RID

    ,DATEADD(HOUR,NM.N,@DATE_FROM) AS DATE_HOUR

    ,PL.ProductId

    FROM NUMS NM

    CROSS APPLY PRODUCT_LIST PL

    WHERE CONVERT(DATE,DATEADD(HOUR,NM.N,@DATE_FROM),0) IN

    (SELECT PR_DATE FROM PRODUCT_DATES)

    )

    SELECT

    PC.ProductId

    ,PC.DATE_HOUR

    ,PL.Amt

    FROM CALENDAR_TABLE PC

    LEFT OUTER JOIN PRODUCT_LOG PL

    ON PC.ProductId = PL.ProductId

    AND PC.DATE_HOUR = PL.dt

    ;

    Results

    ProductId DATE_HOUR Amt

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

    1 2013-12-10 04:00:00.000 220.50

    2 2013-12-10 04:00:00.000 20.10

    1 2013-12-10 05:00:00.000 NULL

    2 2013-12-10 05:00:00.000 NULL

    1 2013-12-10 06:00:00.000 24.50

    2 2013-12-10 06:00:00.000 24.20

    1 2013-12-10 07:00:00.000 527.50

    2 2013-12-10 07:00:00.000 52.40

    1 2013-12-10 08:00:00.000 28.50

    2 2013-12-10 08:00:00.000 8.50

    1 2013-12-10 09:00:00.000 NULL

    2 2013-12-10 09:00:00.000 NULL

    1 2013-12-10 10:00:00.000 NULL

    2 2013-12-10 10:00:00.000 NULL

    1 2013-12-10 11:00:00.000 25.50

    2 2013-12-10 11:00:00.000 5.50

    1 2013-12-10 12:00:00.000 NULL

    2 2013-12-10 12:00:00.000 NULL

    1 2013-12-10 13:00:00.000 NULL

    2 2013-12-10 13:00:00.000 NULL

    1 2013-12-10 14:00:00.000 22.50

    2 2013-12-10 14:00:00.000 2.50

    1 2013-12-10 15:00:00.000 NULL

    2 2013-12-10 15:00:00.000 NULL

    1 2013-12-10 16:00:00.000 NULL

    2 2013-12-10 16:00:00.000 5.77

    1 2013-12-10 17:00:00.000 NULL

    2 2013-12-10 17:00:00.000 NULL

    1 2013-12-10 18:00:00.000 NULL

    2 2013-12-10 18:00:00.000 NULL

    1 2013-12-10 19:00:00.000 NULL

    2 2013-12-10 19:00:00.000 NULL

    1 2013-12-10 20:00:00.000 241.50

    2 2013-12-10 20:00:00.000 241.50

    1 2013-12-10 21:00:00.000 NULL

    2 2013-12-10 21:00:00.000 NULL

    1 2013-12-10 22:00:00.000 NULL

    2 2013-12-10 22:00:00.000 NULL

    1 2013-12-10 23:00:00.000 NULL

    2 2013-12-10 23:00:00.000 NULL

    1 2014-12-10 00:00:00.000 NULL

    2 2014-12-10 00:00:00.000 NULL

    1 2014-12-10 01:00:00.000 NULL

    2 2014-12-10 01:00:00.000 NULL

    1 2014-12-10 02:00:00.000 NULL

    2 2014-12-10 02:00:00.000 NULL

    1 2014-12-10 03:00:00.000 NULL

    2 2014-12-10 03:00:00.000 NULL

    1 2014-12-10 04:00:00.000 NULL

    2 2014-12-10 04:00:00.000 NULL

    1 2014-12-10 05:00:00.000 NULL

    2 2014-12-10 05:00:00.000 NULL

    1 2014-12-10 06:00:00.000 667.50

    2 2014-12-10 06:00:00.000 667.50

    1 2014-12-10 07:00:00.000 NULL

    2 2014-12-10 07:00:00.000 NULL

    1 2014-12-10 08:00:00.000 444.50

    2 2014-12-10 08:00:00.000 444.50

    1 2014-12-10 09:00:00.000 67.50

    2 2014-12-10 09:00:00.000 67.50

    1 2014-12-10 10:00:00.000 NULL

    2 2014-12-10 10:00:00.000 NULL

    1 2014-12-10 11:00:00.000 28.50

    2 2014-12-10 11:00:00.000 255.50

    1 2014-12-10 12:00:00.000 25.50

    2 2014-12-10 12:00:00.000 25.50

    1 2014-12-10 13:00:00.000 NULL

    2 2014-12-10 13:00:00.000 NULL

    1 2014-12-10 14:00:00.000 NULL

    2 2014-12-10 14:00:00.000 44.00

    1 2014-12-10 15:00:00.000 NULL

    2 2014-12-10 15:00:00.000 NULL

    1 2014-12-10 16:00:00.000 NULL

    2 2014-12-10 16:00:00.000 NULL

    1 2014-12-10 17:00:00.000 82.50

    2 2014-12-10 17:00:00.000 82.50

    1 2014-12-10 18:00:00.000 NULL

    2 2014-12-10 18:00:00.000 NULL

    1 2014-12-10 19:00:00.000 NULL

    2 2014-12-10 19:00:00.000 NULL

    1 2014-12-10 20:00:00.000 NULL

    2 2014-12-10 20:00:00.000 55.00

    1 2014-12-10 21:00:00.000 NULL

    2 2014-12-10 21:00:00.000 NULL

    1 2014-12-10 22:00:00.000 221.50

    2 2014-12-10 22:00:00.000 231.50

  • clayman (12/10/2014)


    Hello,

    See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year. Thank you

    I'm a bit confused by the sample data and some of the examples that some of the other folks have given so let me go direct to the horse's mouth and ask you...

    In your original post (quoted above), you say that you "want hourly breakdown for the last X years, the month and day will be the same for each year".

    [font="Arial Black"]If this were a stored procedure or function, what would you use for criteria inputs (variables expected) to the stored procedure or function?[/font] I ask because I see that all of your examples are based on just one month or day.

    You also don't mention how you'd like to handle February 29th.

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

  • Are you able to build a calendar or "dimDate" table? That will simplify this and similar queries that come up in the future.

    Don Simpson



    I'm not sure about Heisenberg.

  • DonlSimpson (12/16/2014)


    Are you able to build a calendar or "dimDate" table? That will simplify this and similar queries that come up in the future.

    +1

    for any warehouse / reporting this is what I commend

    😎

Viewing 12 posts - 1 through 11 (of 11 total)

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