Get hour from datetime range SQL server 2008

Question

I have table containing branch id and branch starttime and endtime

  BRANCHID   SHIFTID        STARTTIME               STOPTIME        
    1               1   1900-01-01 00:01:00.000 1900-01-01 23:58:00.000 
    4               4   1900-01-01 07:30:00.000 1900-01-01 18:00:00.000 
    5               5   1900-01-01 06:30:00.000 1900-01-01 19:00:00.000 
    6               6   1900-01-01 06:30:00.000 1900-01-01 17:00:00.000 
    7               7   1900-01-01 00:30:00.000 1900-01-01 18:00:00.000

Now i want to get the number of hours in date range like :

BRANCHID              Hours     
        1               1   
        1               2  
        1               3
        .               .
        .               .

The resultant table containing branch id and hours in time interval. like branch 1 start time is 00:01:00 - 23:58:00. Than branch id 1 time interval contains following hours 1,2,3,4,5,6 and so on..

please explain the result and tag the version of SQL server you are using — TheGameiswar, Sep 26 '16 at 13:28

score 1 · Answer 1 · edited Nov 13 '18 at 22:55

1

select 
branchid from table t1
cross apply
(
select n from numbers
where n >=datepart(hour,starttime) and n<=datepart(hour,stoptime)
) b

Below are some links on the numbers table used..

1.http://dataeducation.com/you-require-a-numbers-table/

2.https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

edited Nov 13 '18 at 22:55

Community

1
1

answered Sep 26 '16 at 13:36

TheGameiswar

27,855
8
56
94

2

Never leave home without your numbers table – CaptainMarvel Sep 26 '16 at 13:41
FYI | Great for a simple numbers table where you don't have `CREATE` permissions: [Generate a number sequence](http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server). Just change the `SELECT` statement to a `SELECT * INTO #numbers`. This has helped on contract jobs where I only had basic rights. – CaptainMarvel Sep 26 '16 at 13:56

score 0 · Accepted Answer · answered Sep 27 '16 at 05:54

Create temp table #hours.

create table #Hours
    (
    BId int,
    [Hour] int
    )

Get max and min datetime.

While (@minDateTime < @maxDateTime)
begin
   insert into #Hours values (@brID,Datepart(hour,@minDateTime))
   set @minDateTime = DATEADD(hh,1,@minDateTime)
end 

Select * from #Hours

Get hour from datetime range SQL server 2008

2 Answers2