我需要計算多行之間的日期差異。場景是我有一輛可以在整個月進行檢查的車輛，也可以在將車輛分配給不同的專案時進行檢查。我想計算每月或上個月將車輛分配給專案的天數。我嘗試了多種方法，但我無法更接近。我對堆疊溢位比較陌生。如有遺漏，請見諒。請讓我知道這是否可以完成。謝謝你。
如果有幫助，所有列都在一個表中。請讓我知道有關如何實作此目標的查詢
我正在使用 SQL Server 2017。
原始資料
預期產出
uj5u.com熱心網友回復：
我并不為這個解決方案感到自豪，但我認為它對你有用。我的方法是創建一個天數表，然后查看每天將車輛分配到哪個專案。最后，按月和年聚合以獲得結果。我不得不將其作為腳本來執行，因為您可以在遞回 CTE 的定義中使用聚合函式，但是您可能會找到一種無需遞回 CTE 的方法來執行此操作。
我創建了一個表變數來匯入你的資料，所以我可以寫這個。請注意，我為跨越數月的測驗作業添加了一個額外的作業。
DECLARE @Vehicles AS TABLE
(
[VehicleID] INT NOT NULL,
[Project] CHAR(2) NOT NULL,
[InspectionDate] DATE NOT NULL
);
INSERT INTO @Vehicles
(
[VehicleID],
[Project],
[InspectionDate]
)
VALUES
(1, 'P1', '2021-08-20'),
(1, 'P1', '2021-09-05'),
(1, 'P2', '2021-09-15'),
(1, 'P3', '2021-09-20'),
(1, 'P2', '2021-10-10'),
(1, 'P1', '2021-10-20'),
(1, 'P3', '2021-10-21'),
(1, 'P2', '2021-10-22'),
(1, 'P4', '2021-11-15'),
(1, 'P4', '2021-11-25'),
(1, 'P4', '2021-11-30'),
(1, 'P1', '2022-02-05');
DECLARE @StartDate AS DATE, @EndDate AS DATE;
SELECT @StartDate = MIN([InspectionDate]), @EndDate = MAX([InspectionDate])
FROM @Vehicles;
;WITH [seq]([n])
AS (SELECT 0 AS [n]
UNION ALL
SELECT [n] 1
FROM [seq]
WHERE [n] < DATEDIFF(DAY, @StartDate, @EndDate)),
[days]
AS (SELECT DATEADD(DAY, [n], @StartDate) AS [d]
FROM [seq]),
[inspections]
AS (SELECT [VehicleID],
[Project],
[InspectionDate],
LEAD([InspectionDate], 1) OVER (PARTITION BY [VehicleID]
ORDER BY [InspectionDate]
) AS [NextInspectionDate]
FROM @Vehicles),
[assignmentsByDay]
AS (SELECT [d].[d], [i].[VehicleID], [i].[Project]
FROM [days] AS [d]
INNER JOIN [inspections] AS [i]
ON [d].[d] >= [i].[InspectionDate]
AND [d] < [i].[NextInspectionDate])
SELECT [assignmentsByDay].[VehicleID],
[assignmentsByDay].[Project],
MONTH([assignmentsByDay].[d]) AS [month],
YEAR([assignmentsByDay].[d]) AS [year],
COUNT(*) AS [daysAssigned]
FROM [assignmentsByDay]
GROUP BY [assignmentsByDay].[VehicleID],
[assignmentsByDay].[Project],
MONTH([assignmentsByDay].[d]),
YEAR([assignmentsByDay].[d])
ORDER BY [year], [month], [assignmentsByDay].[VehicleID], [assignmentsByDay].[Project]
OPTION(MAXRECURSION 0);
輸出是：
| 車輛ID | 專案 | 月 | 年 | 分配天數 |
|---|---|---|---|---|
| 1 | P1 | 8 | 2021年 | 12 |
| 1 | P1 | 9 | 2021年 | 14 |
| 1 | P2 | 9 | 2021年 | 5 |
| 1 | P3 | 9 | 2021年 | 11 |
| 1 | P1 | 10 | 2021年 | 1 |
| 1 | P2 | 10 | 2021年 | 20 |
| 1 | P3 | 10 | 2021年 | 10 |
| 1 | P2 | 11 | 2021年 | 14 |
| 1 | P4 | 11 | 2021年 | 16 |
| 1 | P4 | 12 | 2021年 | 31 |
| 1 | P4 | 1 | 2022年 | 31 |
| 1 | P4 | 2 | 2022年 | 4 |
uj5u.com熱心網友回復：
我想你正在尋找這個：
select vehicleId
, Project
, month(inspectiondate) month
, year(inspectiondate) year
, datediff(day , min(inspectiondate), case when max(inspectiondate) = min(inspectiondate) then eomonth(min(inspectiondate)) else max(inspectiondate) end) days
from Vehicles
group by vehicleId, Project , month(inspectiondate), year(inspectiondate)
此查詢針對該月/年專案中每個特定車輛的每個月/年，您將獲得最大和最小檢查日期并計算差異。
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/341357.html