How to write this windowing query in T-SQL?

Question

I have some tables to store flying data:

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

This query returns the flight duration and cumulative hours for all wings across all flights:

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

I need a query that, for each flight in the flights table, ordered by flights.Id, the cumulative hours for each wingId since the last service date from wingServiceHistory or since the purchase date in wings whichever is sooner.

Or, in English, for every flight, how many hours has this wing been flown since the last service, or the number of hours since it was purchased including the hours when bought.

Results set desired is every column in the flights table plus duration, cumulativeHours, cumulativeHoursSinceService

I would expect results to look like

Id	Date	TakeOffTime	LandingTime	WingId	duration (HH:MM)	cumulativeHours	cumulativeHoursSinceService
1	2019-09-02	10:00	12:00	3	02:00	2.0	2.0
2	2019-09-03	09:30	12:30	3	03:00	5.0	5.0
3	2020-05-05	07:00	08:45	3	01:45	6.75	1.75
4	2020-09-28	13:00	15:00	3	02:00	8.75	2.0
5	2021-01-03	17:00	19:00	8	02:00	10.75	152.0
6	2021-01-05	15:30	17:00	8	01:30	12.25	153.5
7	2021-08-25	06:00	08:00	8	02:00	14.25	2.0
8	2021-08-26	07:00	09:30	3	02:30	16.75	4.75
9	2021-09-01	06:00	07:00	8	01:00	17.75	3.0
10	2022-08-10	07:00	09:00	8	02:00	19.75	2.0
11	2022-10-17	15:00	17:00	13	02:00	21.75	5.0
12	2022-10-19	16:00	18:00	8	02:00	23.75	4.0
13	2022-12-21	13:00	15:30	13	02:30	26.25	7.5

@YitzhakKhabinsky all of this was already present. – Patrick Hurst Jun 29 '23 at 15:09 — Patrick Hurst, Jun 29 '23 at 15:09
Correct. I missed the *sample data population*. – Yitzhak Khabinsky Jun 29 '23 at 15:45 — Yitzhak Khabinsky, Jun 29 '23 at 15:45

Patrick Hurst · Accepted Answer · 2023-06-29T17:00:14.083

A good approach for this is a couple of windowed functions to grab what you need. In this case you're also looking for a specific correlated row in the service history, so I opted for an outer apply for that.

SELECT f.Id, f.Date, f.TakeOffTime, f.LandingTime, w.id AS WingID, CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,TakeOffTime,LandingTime),'00:00:00') AS TIME) AS Duration,
CAST(SUM(DATEDIFF(MINUTE,TakeOffTime,LandingTime)) OVER (ORDER BY f.id)/60.0 AS DECIMAL(4,2)) AS CumulativeHours,
CAST(SUM(DATEDIFF(MINUTE,TakeOffTime,LandingTime)) OVER (PARTITION BY w.id, wsh.date ORDER BY f.id)/60.0 AS DECIMAL(4,2))+CASE WHEN wsh.date IS NULL THEN hoursWhenBought ELSE 0 END AS CumulativeHoursSinceService 
  FROM @flights f
    INNER JOIN @wings w
      ON f.WingId = w.Id
    OUTER APPLY (SELECT TOP 1 * FROM @wingServiceHistory wsh WHERE wingid = w.id and date <= f.Date ORDER BY date) wsh
 ORDER BY f.id

Id	Date	TakeOffTime	LandingTime	WingID	Duration	CumulativeHours	CumulativeHoursSinceService
1	2019-09-02	10:00:00.0000000	12:00:00.0000000	3	02:00:00.0000000	2.00	2.00
2	2019-09-03	09:30:00.0000000	12:30:00.0000000	3	03:00:00.0000000	5.00	5.00
3	2020-05-05	07:00:00.0000000	08:45:00.0000000	3	01:45:00.0000000	6.75	6.75
4	2020-09-28	13:00:00.0000000	15:00:00.0000000	3	02:00:00.0000000	8.75	2.00
5	2021-01-03	17:00:00.0000000	19:00:00.0000000	8	02:00:00.0000000	10.75	152.00
6	2021-01-05	15:30:00.0000000	17:00:00.0000000	8	01:30:00.0000000	12.25	153.50
7	2021-08-25	06:00:00.0000000	08:00:00.0000000	8	02:00:00.0000000	14.25	2.00
8	2021-08-26	07:00:00.0000000	09:30:00.0000000	3	02:30:00.0000000	16.75	4.50
9	2021-09-01	06:00:00.0000000	07:00:00.0000000	8	01:00:00.0000000	17.75	3.00
10	2022-08-10	07:00:00.0000000	09:00:00.0000000	8	02:00:00.0000000	19.75	5.00
11	2022-10-17	15:00:00.0000000	17:00:00.0000000	13	02:00:00.0000000	21.75	5.00
12	2022-10-19	16:00:00.0000000	18:00:00.0000000	8	02:00:00.0000000	23.75	7.00
13	2022-12-21	13:00:00.0000000	15:30:00.0000000	13	02:30:00.0000000	26.25	7.50

Windowed functions allow you to define the window they use by specifying them in the OVER clause:

PARTITION BY - group by these things ORDER BY - in this order

With the SUM for the cumulative hours we're asking for the running total, ordered by flight id.

With the SUM for the hours since service we're again asking for the running total, but this time grouped by the wing id and the service date (so it starts over for each new one) and ordered by flight id.

ValNik · Answer 2 · 2023-06-30T09:20:59.107

Try this example. I think, it is important to calculate resource used after last service and total used resource for every WingId. So, query result differs from desired.
Upd1:hoursWhenBought are considered not serviced (condition1 added to query).

with data as(
select WingId,'flight' EventType,date eventDate,TakeOffTime,LandingTime,id
  ,(datediff(mi,TakeOffTime,LandingTime)
    +case when TakeOffTime>LandingTime then 1440 else 0 end)/60.0  workHours
from flights
union all
select Id as WingId,'purchase' EventType,purchaseDate as eventDate,null,null,0
  ,hoursWhenBought  workHours
from wings
union all
select WingId,'Service' EventType,date eventDate,null,null,0
  ,0 workHours
from wingServiceHistory
)
,LifeCicles as(
select * 
  ,sum(case when eventType='purchase' or eventType='service' then 1 else 0 end)
     over(partition by wingId order by eventDate) cicleN
from data
)
select WingId,EventType,eventDate,TakeOffTime TOff,LandingTime LT,workHours wHours,cicleN
  ,sum(workHours)over(partition by wingId order by eventDate) cumHours
  ,sum(case when eventType='flight' 
               or eventType='purchase'  -- added condition1
           then workHours else 0 end)
      over(partition by wingId,cicleN order by eventDate) cumService
  , id
  ,sum(case when eventType='flight' then workHours else 0 end)
           over(order by eventDate) totHours  -- total for all flights
from LifeCicles
order by wingId,eventDate,id

Result(draft). Here wHours -duration or hoursWhenBought. Column names shorted for clearity.

WingId	EventT.	eventDate	TOff	LandT	wHours	cicleN	cumTotal	cumService	id
2	purchase	2017-11-04	null	null	45.00	1	45.00	45.00	0
3	purchase	2019-08-23	null	null	0.000	1	0.00	0.00	0
3	flight	2019-09-02	10:00:00	12:00:00	2.000	1	2.00	2.00	1
3	flight	2019-09-03	09:30:00	12:30:00	3.000	1	5.00	5.00	2
3	flight	2020-05-05	07:00:00	08:45:00	1.750	1	6.75	6.75	3
3	Service	2020-09-21	null	null	0.000	2	6.75	0.00	0
3	flight	2020-09-28	13:00:00	15:00:00	2.000	2	8.75	2.00	4
3	flight	2021-08-26	07:00:00	09:30:00	2.500	2	11.25	4.50	8
8	purchase	2021-01-02	null	null	150.000	1	150.00	150.00	0
8	flight	2021-01-03	17:00:00	19:00:00	2.000	1	152.00	152.00	5
8	flight	2021-01-05	15:30:00	17:00:00	1.500	1	153.50	153.50	6
8	Service	2021-08-24	null	null	0.000	2	153.50	0.00	0
8	flight	2021-08-25	06:00:00	08:00:00	2.000	2	155.50	2.00	7
8	flight	2021-09-01	06:00:00	07:00:00	1.000	2	156.50	3.00	9
8	Service	2022-08-03	null	null	0.000	3	156.50	0.00	0
8	flight	2022-08-10	07:00:00	09:00:00	2.000	3	158.50	2.00	10
8	flight	2022-10-19	16:00:00	18:00:00	2.000	3	160.50	4.00	12
13	purchase	2022-10-16	null	null	3.000	1	3.00	3.00	0
13	flight	2022-10-17	15:00:00	17:00:00	2.000	1	5.00	5.00	11
13	flight	2022-12-21	13:00:00	15:30:00	2.500	1	7.50	7.50	13

Example fiddle

How to write this windowing query in T-SQL?

2 Answers2