如何在TSQL中编写此窗口查询

我有一些表来存储飞行数据:

CREATE TABLE [dbo].[wings]
(
    [Id] [int] NOT NULL,
    [Manufacturer] [varchar](50) NOT NULL,
    [Model] [varchar](50) NULL,
    [Size] [decimal](3, 1) NULL,
    [hoursWhenBought] [tinyint] NULL,
    [purchaseDate] [date] NULL,

    CONSTRAINT [PK__wings__3214EC07E42B45BC] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[wingServiceHistory]
(
    [wingId] [int] NOT NULL,
    [date] [date] NOT NULL,
    [servicedBy] [varchar](100) NOT NULL,
    [comments] [varchar](200) NULL,

    CONSTRAINT [PK_wingServiceHistory] 
        PRIMARY KEY CLUSTERED ([wingId] ASC, [date] ASC)
)

CREATE TABLE [dbo].[flights]
(
    [Id] [int] NOT NULL,
    [Date] [date] NOT NULL,
    [TakeOffTime] [time](7) NOT NULL,
    [LandingTime] [time](7) NOT NULL,
    [WingId] [int] NULL
)

CREATE CLUSTERED INDEX [ClusteredIndex-Date] 
ON [dbo].[flights] ([Date] ASC)

-- Sample data
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (1, CAST(N'2019-09-02' AS Date), CAST(N'10:00:00' AS Time), CAST(N'12:00:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (2, CAST(N'2019-09-03' AS Date), CAST(N'09:30:00' AS Time), CAST(N'12:30:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (3, CAST(N'2020-05-05' AS Date), CAST(N'07:00:00' AS Time), CAST(N'08:45:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (4, CAST(N'2020-09-28' AS Date), CAST(N'13:00:00' AS Time), CAST(N'15:00:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (5, CAST(N'2021-01-03' AS Date), CAST(N'17:00:00' AS Time), CAST(N'19:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (6, CAST(N'2021-01-05' AS Date), CAST(N'15:30:00' AS Time), CAST(N'17:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (7, CAST(N'2021-08-25' AS Date), CAST(N'06:00:00' AS Time), CAST(N'08:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (8, CAST(N'2021-08-26' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:30:00' AS Time), 3)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (9, CAST(N'2021-09-01' AS Date), CAST(N'06:00:00' AS Time), CAST(N'07:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (10, CAST(N'2022-08-10' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (11, CAST(N'2022-10-17' AS Date), CAST(N'15:00:00' AS Time), CAST(N'17:00:00' AS Time), 13)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (12, CAST(N'2022-10-19' AS Date), CAST(N'16:00:00' AS Time), CAST(N'18:00:00' AS Time), 8)
INSERT INTO [dbo].[flights] ([Id], [Date], [TakeOffTime], [LandingTime], [WingId]) 
VALUES (13, CAST(N'2022-12-21' AS Date), CAST(N'13:00:00' AS Time), CAST(N'15:30:00' AS Time), 13)

INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (2, N'Dudek', N'Synthesis LT', CAST(31.0 AS Decimal(3, 1)), 45, CAST(N'2017-11-04' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (3, N'Dudek', N'Universal 1.1', CAST(28.0 AS Decimal(3, 1)), 0, CAST(N'2019-08-23' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (8, N'Dudek', N'Nucleon XX', CAST(24.0 AS Decimal(3, 1)), 150, CAST(N'2021-01-02' AS Date))
INSERT INTO [dbo].[wings] ([Id], [Manufacturer], [Model], [Size], [hoursWhenBought], [purchaseDate]) 
VALUES (13, N'Dudek', N'Hadron 3', CAST(20.0 AS Decimal(3, 1)), 3, CAST(N'2022-10-16' AS Date))

INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (3, CAST(N'2020-09-21' AS Date), N'Joe Blogs', N'full trim service')
INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (8, CAST(N'2021-08-24' AS Date), N'Joe Blogs', N'full trim service')
INSERT INTO [dbo].[wingServiceHistory] ([wingId], [date], [servicedBy], [comments]) 
VALUES (8, CAST(N'2022-08-03' AS Date), N'Joe Blogs', N'full trim service')

此查询返回所有航班上所有机翼的飞行持续时间和累计小时数:

SELECT
    Id,
    [Date],
    CAST(DATEADD(minute, DATEDIFF(minute, [TakeOffTime], [LandingTime]), 0) AS time) AS Duration,
    CAST ((SUM(DATEDIFF(minute, [TakeOffTime], [LandingTime])) OVER (ORDER BY [Id]) / 60.0) AS DECIMAL(10, 1)) AS CumulativeHours
FROM  
    flights

我需要一个查询，对于flights表中的每个航班，按flights.Id排序，自上次服务日期wingServiceHistory起或自wings年购买日期以来(以较早者为准)，每个wingId航班的累计小时数.

或者，在英语中，对于每一次飞行，这一机翼自上次服务以来飞行了多少小时，或自购买以来的小时数，包括购买时的小时数.

所需的结果集是flights表中的每一列加上duration、cumulativeHours、cumulativeHoursSinceService

我希望结果看起来像是