sql - 计算多行SQL之间的日期差

Question

我需要计算多行之间的日期差异。场景是我有一辆可以在整个月以及当车辆被分配到不同项目时进行检查的车辆。我想计算每月或上个月将车辆分配给项目的天数。我已经尝试了多种方法，但我无法靠近。我对堆栈溢出比较陌生。如果有任何遗漏，我们深表歉意。请让我知道这是否可以做到。谢谢你。

如果有帮助，所有列都在一个表中。请让我知道有关如何实现此目的的查询

我正在使用 SQL Server 2017。

原始数据

预期产出

标签： sqldatabasetsqlsql-server-2017date-difference

Answer 1

我对这个解决方案并不感到自豪，但我认为它对你有用。我的方法是创建一个天数表，然后查看车辆每天分配给哪个项目。最后，按月和年汇总得到结果。我必须将其作为脚本执行，因为您可以在递归 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