How do I solve this complex windowing query in SQL Server?

Question

I have a flight database to track ultralight flights of various aircraft. The motors on these aircraft consist of parts which need replacing at a certain number of flying hours elapsed, which varies depending on the part. My database has the following tables (included below with sample data):

Motors: Information about the motor in the aircraft
MotorParts: A list of parts for each motor and when this part needs replacing
MotorPartServiceHistory: A log of when the part was replaced with the date
Flights: A log of each flight flown by each motor

Views:

FlightDetails: Shows the duration of each flight and the total hours flown for the motor

I need to know when a part on a motor needs replacing.

So, I need a query that returns all the columns from MotorParts with an additional two columns:

HoursSinceReplacement: number of hours flown since last replaced. If NULL then total hours for the motor.
Status: values of either OK or Replace. It should show Replace if HoursSinceReplacement is greater than the ReplaceHours value in MotorParts, otherwise return OK.

I am struggling to figure this out. If you add the MotorParts sample data row by row you should see the Status column changing from OK to Replace each time you run the query.

Schema

DROP VIEW IF EXISTS [dbo].[FlightDetails]
DROP TABLE IF EXISTS [dbo].[Flights]
DROP TABLE IF EXISTS [dbo].[MotorPartServiceHistory]
DROP TABLE IF EXISTS [dbo].[MotorParts]
DROP TABLE IF EXISTS [dbo].[Motors]
GO
CREATE TABLE [dbo].[Motors]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED
  , [Manufacturer] VARCHAR(50) NOT NULL
  , [Model] VARCHAR(50) NOT NULL
  , [Engine] VARCHAR(50) NOT NULL
  , [EngineCC] TINYINT NOT NULL
)
GO
CREATE TABLE [dbo].[MotorParts]
(
    [Id] INT NOT NULL,
    [PartName] VARCHAR(200) NOT NULL, 
    [PartNumber] VARCHAR(100) NULL,
    [MotorId] INT NOT NULL, 
    [ReplaceHours] TINYINT NULL, 
    CONSTRAINT [FK_MotorParts_Motors] FOREIGN KEY ([MotorId]) REFERENCES [dbo].[Motors]([Id]), 
    CONSTRAINT [PK_MotorParts] PRIMARY KEY ([Id]), 
    CONSTRAINT [AK_MotorParts_MotorIdPartName] UNIQUE ([PartName],[MotorId])
)
GO
CREATE UNIQUE INDEX [IX_MotorParts_PartNumber] ON [dbo].[MotorParts] ([PartNumber])
GO
CREATE TABLE [dbo].[MotorPartServiceHistory]
(
    [Id] INT NOT NULL,
    [PartId] INT NOT NULL, 
    [Date] DATE NOT NULL,
    CONSTRAINT [FK_MotorPartServiceHistory_MotorParts] FOREIGN KEY ([PartId]) REFERENCES [dbo].[MotorParts]([Id]), 
    CONSTRAINT [PK_MotorPartServiceHistory] PRIMARY KEY CLUSTERED ([Id])
)
GO
CREATE UNIQUE INDEX [MotorPartServiceHistory_DatePartId] ON [dbo].[MotorPartServiceHistory] ([Date], [PartId])
GO
CREATE TABLE [dbo].[Flights]
(
    [Id] [int] NOT NULL,
    [Date] [date] NOT NULL,
    [TakeOffTime] [time](7) NOT NULL,
    [LandingTime] [time](7) NOT NULL,
    [MotorId] [int] NULL,
    CONSTRAINT [PK_Flights] PRIMARY KEY ([Id])
)
GO
CREATE VIEW [dbo].[FlightDetails]
    AS 

SELECT f.[Id] as [Flight]
    ,f.[Date]
    ,DATENAME (dw, f.[Date]) as DoW
    ,f.[MotorId]
    ,[TakeOffTime]
    ,[LandingTime]
    ,CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, [TakeOffTime], [LandingTime]),'00:00:00') AS time) as Duration
    ,DATEDIFF(MINUTE, [TakeOffTime], [LandingTime]) as DurationMinutes
    ,CAST ((SUM(DATEDIFF(minute, [TakeOffTime], [LandingTime])) OVER (PARTITION BY f.MotorId ORDER BY f.[Id])/60.0) AS DECIMAL(10,1)) as TotalHours
FROM [dbo].[Flights] f
GO

Sample data to play around with

INSERT [dbo].[Motors] ([Id], [Manufacturer], [Model], [Engine], [EngineCC]) VALUES (1, N'Parajet', N'Maverick', N'Moster', 185)
INSERT [dbo].[Motors] ([Id], [Manufacturer], [Model], [Engine], [EngineCC]) VALUES (2, N'Scout', N'Enduro', N'Atom', 80)
GO
INSERT [dbo].[MotorParts] ([Id], [PartName], [PartNumber], [MotorId], [ReplaceHours]) VALUES (1, N'Oil for reduction gear', N'AT160', 2, 10)
INSERT [dbo].[MotorParts] ([Id], [PartName], [PartNumber], [MotorId], [ReplaceHours]) VALUES (2, N'Starter Rope (2m)', N'M043', 1, 20)
INSERT [dbo].[MotorParts] ([Id], [PartName], [PartNumber], [MotorId], [ReplaceHours]) VALUES (3, N'Spark plug', N'M020', 1, 18)
INSERT [dbo].[MotorParts] ([Id], [PartName], [PartNumber], [MotorId], [ReplaceHours]) VALUES (4, N'Exhaust bushing', N'MY202', 1, 25)
GO
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (1, CAST(N'2023-08-01' AS Date), CAST(N'17:00:00' AS Time), CAST(N'20:00:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (2, CAST(N'2023-08-02' AS Date), CAST(N'06:00:00' AS Time), CAST(N'10:00:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (3, CAST(N'2023-08-04' AS Date), CAST(N'07:00:00' AS Time), CAST(N'08:30:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (4, CAST(N'2023-08-05' AS Date), CAST(N'09:00:00' AS Time), CAST(N'13:00:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (5, CAST(N'2023-08-06' AS Date), CAST(N'10:00:00' AS Time), CAST(N'16:00:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (6, CAST(N'2023-08-08' AS Date), CAST(N'05:30:00' AS Time), CAST(N'07:30:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (7, CAST(N'2023-08-09' AS Date), CAST(N'09:45:00' AS Time), CAST(N'12:15:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (8, CAST(N'2023-08-10' AS Date), CAST(N'16:15:00' AS Time), CAST(N'20:00:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (9, CAST(N'2023-08-11' AS Date), CAST(N'12:00:00' AS Time), CAST(N'17:00:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (10, CAST(N'2023-08-12' AS Date), CAST(N'06:00:00' AS Time), CAST(N'10:00:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (11, CAST(N'2023-08-13' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:00:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (12, CAST(N'2023-08-14' AS Date), CAST(N'08:15:00' AS Time), CAST(N'12:00:00' AS Time), 2)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (13, CAST(N'2023-08-15' AS Date), CAST(N'09:45:00' AS Time), CAST(N'11:20:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (14, CAST(N'2023-08-16' AS Date), CAST(N'10:00:00' AS Time), CAST(N'14:00:00' AS Time), 1)
INSERT [dbo].[Flights] ([Id], [Date], [TakeOffTime], [LandingTime], [MotorId]) VALUES (15, CAST(N'2023-08-17' AS Date), CAST(N'08:00:00' AS Time), CAST(N'11:00:00' AS Time), 1)
GO
INSERT [dbo].[MotorPartServiceHistory] ([Id], [PartId], [Date]) VALUES (1, 1, CAST(N'2023-08-08' AS Date))
INSERT [dbo].[MotorPartServiceHistory] ([Id], [PartId], [Date]) VALUES (2, 3, CAST(N'2023-08-14' AS Date))
INSERT [dbo].[MotorPartServiceHistory] ([Id], [PartId], [Date]) VALUES (3, 2, CAST(N'2023-08-16' AS Date))
INSERT [dbo].[MotorPartServiceHistory] ([Id], [PartId], [Date]) VALUES (4, 4, CAST(N'2023-08-18' AS Date))
GO

Expected results

If MotorPartServiceHistory is empty the results should be

Id	PartName	PartNumber	MotorId	ReplaceHours	HoursSinceReplacement	Status
1	Oil for reduction gear	AT160	2	10	21.75	Replace
2	Starter Rope (2m)	M043	1	20	28.3	Replace
3	Spark plug	M020	1	18	28.3	Replace
4	Exhaust bushing	MY202	1	25	28.3	Replace

After entering Id=1 from the sample data in MotorPartServiceHistory the query should look like:

Id	PartName	PartNumber	MotorId	ReplaceHours	HoursSinceReplacement	Status
1	Oil for reduction gear	AT160	2	10	9.75	OK
2	Starter Rope (2m)	M043	1	20	28.3	Replace
3	Spark plug	M020	1	18	28.3	Replace
4	Exhaust bushing	MY202	1	25	28.3	Replace

After entering Id=2 from the sample data in MotorPartServiceHistory the query should look like:

Id	PartName	PartNumber	MotorId	ReplaceHours	HoursSinceReplacement	Status
1	Oil for reduction gear	AT160	2	10	9.75	OK
2	Starter Rope (2m)	M043	1	20	8.6	OK
3	Spark plug	M020	1	18	28.3	Replace
4	Exhaust bushing	MY202	1	25	28.3	Replace

After entering Id=3 from the sample data in MotorPartServiceHistory the query should look like:

Id	PartName	PartNumber	MotorId	ReplaceHours	HoursSinceReplacement	Status
1	Oil for reduction gear	AT160	2	10	9.75	OK
2	Starter Rope (2m)	M043	1	20	8.6	OK
3	Spark plug	M020	1	18	3	OK
4	Exhaust bushing	MY202	1	25	28.3	Replace

After entering Id=4 from the sample data in MotorPartServiceHistory the query should look like:

Id	PartName	PartNumber	MotorId	ReplaceHours	HoursSinceReplacement	Status
1	Oil for reduction gear	AT160	2	10	9.75	OK
2	Starter Rope (2m)	M043	1	20	8.6	OK
3	Spark plug	M020	1	18	3	OK
4	Exhaust bushing	MY202	1	25	0	OK

Where is it, exactly, you are getting stuck? What is your attempt so far? You've provided sample data, in a consumable format (***thank you***) but no expected results for that data either. — Thom A, Aug 18 '23 at 13:09
Thanks for looking into this, I have provided sample output. — Mark Allison, Aug 18 '23 at 15:50

Dmitry · Accepted Answer · 2023-08-19T00:27:45.723

Well, it still can be done without window functions. The query is split for the sake of readability:

WITH TimeSinceReplacement AS (
    SELECT
        MotorParts.Id AS Id,
        SUM(DATEDIFF(MINUTE, TakeOffTime, LandingTime)) AS TotalMinutes
    FROM
        MotorParts AS MotorParts
        INNER JOIN Flights AS FlightHistory
            ON MotorParts.MotorId = FlightHistory.MotorId
        LEFT JOIN (
            -- Query the last service date for each motor part
            SELECT 
                ServiceHistory.PartId AS PartId,
                MAX(ServiceHistory.Date) AS ServiceDate
            FROM 
                MotorPartServiceHistory AS ServiceHistory
            GROUP BY 
                ServiceHistory.PartId) AS LastServiceDate
        ON  LastServiceDate.PartId = MotorParts.Id
    WHERE 
        -- Leave only the flight records, 
        -- which are strictly after the last service date
        ISNULL(LastServiceDate.ServiceDate, '0001-01-01') < FlightHistory.Date
    GROUP BY
        MotorParts.Id)

SELECT
    MotorParts.Id AS Id,
    MotorParts.PartName AS PartName,
    MotorParts.PartNumber AS PartNumber,  
    MotorParts.MotorId AS MotorId,
    MotorParts.ReplaceHours AS ReplaceHours,
    CAST(
        ISNULL(TimeSinceReplacement.TotalMinutes, 0) / 60.0 
        AS DECIMAL(6,2)) AS HoursSinceReplacement,
    CASE 
        WHEN ISNULL(TimeSinceReplacement.TotalMinutes, 0) 
                        > MotorParts.ReplaceHours * 60 
        THEN 'Replace' 
        ELSE 'OK' 
    END AS [Status]
 FROM 
    MotorParts AS MotorParts
    -- There won't be replacement records, 
    -- if maintenance was not after the last flight
    LEFT JOIN TimeSinceReplacement AS TimeSinceReplacement
        ON MotorParts.Id = TimeSinceReplacement.Id

Few remarks:

Ids in sample service history do not follow the description: records with ids 2 and 3 lead to the reversed parts: 3 and 2.
20 minutes are converted to 0.33 hours and 35 minutes - to 0.58 hours. In contrary to the expected results, which are 0.3 and 0.6.

How do I solve this complex windowing query in SQL Server?

1 Answers1