CODEWITHSUNDEEP
sql-server
Mahitha Admala
Mahitha Admala

Reputation: 29

Insert a row for every hour in a date range

I have a table with a StartDate column and an EndDate column. I need to insert into a new table a row for each hour in the date range of the above tables column. The table I have looks like this
StartDate EndDate
2017-10-25 19:00:00.000 2017-11-30 23:59:59.997
2017-10-26 13:00:00.000 2017-12-1 23:59:59.997

new table I need should look like this
Date Hour
2017-10-25 19
2017-10-25 20
2017-10-25 21
2017-10-25 22
2017-10-25 23
2017-10-26 0
2017-10-26 1
2017-10-26 2
:::::::::: :
:::::::::: :
2017-11-30 22
2017-11-30 23

I am so lost, please help!

Upvotes: 2

Views: 1085

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Can be done with an ad-hoc tally table in concert with a CROSS APPLY.

Example

Select Date = cast(D as date)
      ,Hour = datepart(HOUR,D) 
 From YourTable A
 Cross Apply (
                Select Top (DateDiff(HOUR,A.StartDate,A.EndDate)+1) D=DateAdd(HOUR,-1+Row_Number() Over (Order By (Select Null)),A.StartDate) 
                 From  master..spt_values n1,master..spt_values n2
             ) B

Returns

Date        Hour
2017-10-25  19
2017-10-25  20
2017-10-25  21
2017-10-25  22
2017-10-25  23
2017-10-26  0
2017-10-26  1
2017-10-26  2
2017-10-26  3
...

Upvotes: 1

Related Questions