How to get COUNT of a range between 2 columns Table:

Staff  | StartingTime |  EndingTime  | starthour   | endhour
-------------------------------------------------------------
10     |     0813     |    1610      |    08       |    16 
20     |     0910     |    1224      |    09       |    12
30     |     1132     |    1706      |    11       |    17
40     |     1311     |    2110      |    13       |    21
50     |     1533     |    2230      |    15       |    22


The first 2 time columns are nvarchar(4) and I derived the last 2 columns with LEFT(field, 2) to get the hours.

I need to get a count of the number of staff that were working for each hour. It will need to by dynamic as I will never know the time ranges. Right now I'm just trying to get this working for a single day, but eventually I need it broken out by day.

Expected results:

Hour  |  Staff 
------------------
8     |    1
9     |    2
10    |    2
11    |    3
12    |    3
13    |    4
14    |    4
15    |    5
..
19    |    2
etc..


This is on Microsoft SQL Server 2014.
This is a way of doing it. However, if you need it broken out by day, you need to add an extra column in your table for indicating the day, and you should add that column to the table variable I created as well.

    DECLARE @Hours TABLE
    (
      hour varchar(2),
      total int
    )
    DECLARE @I INT 
    SET @I = 0
    WHILE @I &lt; 24
    begin
        INSERT INTO @Hours VALUES (RIGHT('0' + CAST(@I AS VARCHAR(2)),2), 0)
        SET @I = @I + 1
    end 


    DECLARE @starthour VARCHAR(2)
    DECLARE @endhour VARCHAR(2)
    DECLARE cur CURSOR
    FOR SELECT starthour, endhour FROM WorkTime
    OPEN cur
    FETCH NEXT FROM cur
    INTO @starthour, @endhour
    WHILE @@FETCH_STATUS = 0
    begin
        UPDATE @hours
        SET total = total + 1
        WHERE hour BETWEEN @starthour AND @endhour
        FETCH NEXT FROM cur INTO @starthour, @endhour
    end
    CLOSE cur
    DEALLOCATE cur

SELECT * FROM @hours
WHERE total &lt;&gt; 0


OUTPUT

hour    total
08      1
09      2
10      2
11      3
12      3
13      3
14      3
15      4
16      4
17      3
18      2
19      2
20      2
21      2
22      1