如何獲取 2 列之間範圍的 COUNT

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

前 2 個時間列是 nvarchar(4)，我派生了最後 2 個列LEFT(field, 2)來獲取小時數。

我需要計算每小時工作的員工人數。它需要動態，因為我永遠不會知道時間範圍。現在我只是想讓它工作一天，但最終我需要它在一天之內打破。

預期成績：

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

這是在 Microsoft SQL Server 2014 上。

使用 a在andRecursive CTE之間生成 HoursMin starthour``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] 

這是一種方法。但是，如果您需要按天細分，則需要在表中添加一個額外的列來指示日期，並且您應該將該列添加到我創建的表變數中。

   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

輸出

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

引用自：https://dba.stackexchange.com/questions/86078