Filling gaps in date ranges

Question

Post reply

Filling gaps in date ranges

kiril.lazarov.77

Hall of Fame

Points: 3742
More actions
December 10, 2014 at 3:25 pm

#292716

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

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

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

You need to use a numbers or tally table as the main table in your query. Check out this article on the topic. http://qa.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/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 4

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!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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)
Intro to Tally Tables and Functions

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 6

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

pietlinden SSC Guru Points: 63198 More actions · Answer 7

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?

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

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)
Intro to Tally Tables and Functions

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 10

DonlSimpson

SSCertifiable

Points: 7043

December 16, 2014 at 9:38 am

#1764962

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.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 11

Eirikur Eiriksson

SSC Guru

Points: 182631

December 16, 2014 at 10:14 am

#1764971

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

😎