Get hour from datetime range SQL server 2008
<p>I have table containing branch id and branch starttime and endtime  </p>

<pre><code>  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 
</code></pre>

<p>Now i want to get the number of hours in date range like :</p>

<pre><code>BRANCHID              Hours     
        1               1   
        1               2  
        1               3
        .               .
        .               .
</code></pre>

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