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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  December 10, 2014 at 3:28 pm

  #1763921

  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/

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  December 10, 2014 at 4:51 pm

  #1763933

  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: 1004363

  More actions

  December 10, 2014 at 6:11 pm

  #1763937

  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)
  

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  December 10, 2014 at 10:37 pm

  #1763948

  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: 1004363

  More actions

  December 10, 2014 at 11:16 pm

  #1763949

  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)
  

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  December 13, 2014 at 11:38 am

  #1764612

  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: 63503

  More actions

  December 14, 2014 at 2:24 am

  #1764626

  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: 182912

  More actions

  December 14, 2014 at 5:05 am

  #1764629

  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: 1004363

  More actions

  December 14, 2014 at 10:04 am

  #1764633

  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)
  

• DonlSimpson

  SSCertifiable

  Points: 7043

  More actions

  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: 182912

  More actions

  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

  😎