我正在嘗試計算一個地區每個設施每周的運營小時數。我正在努力解決的部分是每天有多個重疊的程式會影響總小時數。
這是我正在使用的表的示例：
| 地點 | 程式 | 日期 | 開始時間 | 時間結束 |
|---|---|---|---|---|
| 一個 | 1 | 09-22-21 | 14:45:00 | 15:45:00 |
| 一個 | 2 | 09-22-21 | 15:30:00 | 16:30:00 |
| b | 88 | 09-22-21 | 10:45:00 | 12:45:00 |
| b | 89 | 09-22-21 | 10:45:00 | 14:45:00 |
我希望得到：
| 地點 | 營業時間 |
|---|---|
| 一個 | 1.75 |
| b | 4 |
我嘗試將 SUM DATEDIFF 與一些 WHERE 陳述句一起使用，但無法使它們起作用。我發現的是如何識別重疊范圍（從同一個表中檢測重疊日期范圍），而不是如何對差異求和以獲得總非重疊操作小時數的預期結果。
uj5u.com熱心網友回復：
相信您正在嘗試確定每個位置的總營業時間。現在因為某些程式可能重疊，您想排除這些程式。為此，我為日期中每個可能的 15 分鐘增量生成一個計數表，然后計算程式運行的時間段
確定每個日期的總營業時間
DROP TABLE IF EXISTS #OperationSchedule
CREATE TABLE #OperationSchedule (ID INT IDENTITY(1,1),Location CHAR(1),Program INT,OpDate DATE,OpStart TIME(0),OpEnd TIME(0))
INSERT INTO #OperationSchedule
VALUES ('a',1,'09-22-21','14:45:00','15:45:00')
,('a',2,'09-22-21','15:30:00','16:30:00')
,('b',88,'09-22-21','10:45:00','12:45:00')
,('b',89,'09-22-21','10:45:00','14:45:00');
/*1 row per 15 minute increment in a day*/
;WITH cte_TimeIncrement AS (
SELECT StartTime = CAST('00:00' AS TIME(0))
UNION ALL
SELECT DATEADD(minute,15,StartTime)
FROM cte_TimeIncrement
WHERE StartTime < '23:45'
),
/*1 row per date in data*/
cte_DistinctDate AS (
SELECT OpDate
FROM #OperationSchedule
GROUP BY OpDate
),
/*Cross join to generate 1 row for each time increment*/
cte_DatetimeIncrement AS (
SELECT *
FROM cte_DistinctDate
CROSS JOIN cte_TimeIncrement
)
/*Join and count each time interval that has a match to identify times when location is operating*/
SELECT Location
,A.OpDate
,HoursOfOperation = CAST(COUNT(DISTINCT StartTime) * 15/60.0 AS Decimal(4,2))
FROM cte_DatetimeIncrement AS A
INNER JOIN #OperationSchedule AS B
ON A.OpDate = B.OpDate
AND A.StartTime >= B.OpStart
AND A.StartTime < B.OpEnd
GROUP BY Location,A.OpDate
uj5u.com熱心網友回復：
這是一種替代方法，無需四舍五入到最接近的 15 分鐘增量：
Declare @OperationSchedule table (
ID int Identity(1, 1)
, Location char(1)
, Program int
, OpDate date
, OpStart time(0)
, OpEnd time(0)
);
Insert Into @OperationSchedule (Location, Program, OpDate, OpStart, OpEnd)
Values ('a', 1, '09-22-21', '14:45:00', '15:45:00')
, ('a', 2, '09-22-21', '15:30:00', '16:30:00')
, ('b', 88, '09-22-21', '10:45:00', '12:45:00')
, ('b', 89, '09-22-21', '10:45:00', '14:45:00')
, ('c', 23, '09-22-21', '12:45:00', '13:45:00')
, ('c', 24, '09-22-21', '14:45:00', '15:15:00')
, ('3', 48, '09-22-21', '09:05:00', '13:55:00')
, ('3', 49, '09-22-21', '14:25:00', '15:38:00')
;
With overlappedData
As (
Select *
, overlap_op = lead(os.OpStart, 1, os.OpEnd) Over(Partition By os.Location Order By os.ID)
From @OperationSchedule os
)
Select od.Location
, start_date = min(od.OpStart)
, end_date = max(iif(od.OpEnd < od.overlap_op, od.OpEnd, od.overlap_op))
, hours_of_operation = sum(datediff(minute, od.OpStart, iif(od.OpEnd < od.overlap_op, od.OpEnd, od.overlap_op)) / 60.0)
From overlappedData od
Group By
od.Location;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425371.html
上一篇：為具有指定值的行分配連續ID