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

  • 1
    So if you need it broken out by day there must be a date part somewhere? – Martin Smith Dec 12 '14 at 23:47
  • And why are you storing the time part as a string? – Aaron Bertrand Dec 13 '14 at 0:40
  • I have no control, it comes from a time clock. – Kevin Dec 14 '14 at 2:30
1

Use a Recursive CTE to generate Hours between Min starthour and Max endhour

CREATE TABLE #temp
  (Staff     INT,starthour INT,endhour   INT )

INSERT #temp
VALUES (10,08,16 ),(20,09,12),(30,11,17),
       (40,13,21),(50,15,22)

DECLARE @max_hr INT =(SELECT Max(endhour)
  FROM   #temp);

WITH cte
     AS (SELECT Min(starthour) [hours]
         FROM   #temp
         UNION ALL
         SELECT [hours] + 1
         FROM   cte
         WHERE  [hours] < @max_hr)
SELECT Count(Staff),
       [hours]
FROM   cte a
       JOIN #temp t
         ON a.[hours] BETWEEN t.starthour AND t.endhour
GROUP  BY [hours] 
  • I got this one working after CASTing the 2 digit hours, – Kevin Dec 16 '14 at 20:50
  • I must admit that this solution is better tahn I previously submitted. More elegant and more efficient. – JGA Dec 16 '14 at 22:21
1

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 < 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 <> 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
  • As you can see, the output differs from the expected results you posted, but I suppose that the results that you posted were simply for explanation purposes and not intended to be 100% correct – JGA Dec 13 '14 at 0:41
  • That was quick. I'll give this a try tomorrow. Appreciate the help, this was giving me fits. – Kevin Dec 14 '14 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.