CODEWITHSUNDEEP
sql-server
shujaat siddiqui
shujaat siddiqui

Reputation: 1577

Get hour from datetime range SQL server 2008

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

Upvotes: 3

Views: 5674

Answers (2)

shujaat siddiqui
shujaat siddiqui

Reputation: 1577

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

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 1

Related Questions