I need to calculate the date difference between multiple rows. The scenario is I have a vehicle that can do inspections throughout the month as well as when the vehicle is assigned to a different project. I want to calculate that how many days that a vehicle is assigned to the project per month or previous month. I have tried multiple ways and I can't get even closer. I am relatively new to stack overflow. Apologies if anything is missing. Please let me know if this can be done. Thank you.

All the columns are in one single table if that helps. Please let me know the query on how to achieve this

I am using SQL server 2017.

Original Data

Expected Output

CodePudding user response：

I am not proud of this solution, but I think it works for you. My approach was to create a table of days and then look at which project the vehicle was assigned to each day. Finally, aggregate by month and year to get the results. I had to do this as a script since you can use aggregate functions in the definitions of recursive CTEs, but you may find a way to do this without needing a recursive CTE.

I created a table variable to import your data so I could write this. Note, I added an extra assignment to test assignments that spanned months.

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);

And the output is:

CodePudding user response：

I think you are looking for this:

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)

This query in for each month/year for each specific vehicle in a project in that month/year , you get the max and min inspection date and calculate the difference.

db<>fiddle here