SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
,(2, CAST(N'2019-09-03' AS Date), CAST(N'09:30:00' AS Time), CAST(N'12:30:00' AS Time), 3)
,(3, CAST(N'2020-05-05' AS Date), CAST(N'07:00:00' AS Time), CAST(N'08:45:00' AS Time), 3)
,(4, CAST(N'2020-09-28' AS Date), CAST(N'13:00:00' AS Time), CAST(N'15:00:00' AS Time), 3)
,(5, CAST(N'2021-01-03' AS Date), CAST(N'17:00:00' AS Time), CAST(N'19:00:00' AS Time), 8)
,(6, CAST(N'2021-01-05' AS Date), CAST(N'15:30:00' AS Time), CAST(N'17:00:00' AS Time), 8)
,(7, CAST(N'2021-08-25' AS Date), CAST(N'06:00:00' AS Time), CAST(N'08:00:00' AS Time), 8)
,(8, CAST(N'2021-08-26' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:30:00' AS Time), 3)
,(9, CAST(N'2021-09-01' AS Date), CAST(N'06:00:00' AS Time), CAST(N'07:00:00' AS Time), 8)
,(10, CAST(N'2022-08-10' AS Date), CAST(N'07:00:00' AS Time), CAST(N'09:00:00' AS Time), 8)
,(11, CAST(N'2022-10-17' AS Date), CAST(N'15:00:00' AS Time), CAST(N'17:00:00' AS Time), 13)
,(12, CAST(N'2022-10-19' AS Date), CAST(N'16:00:00' AS Time), CAST(N'18:00:00' AS Time), 8)
,(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))
,(3, N'Dudek', N'Universal 1.1', CAST(28.0 AS Decimal(3, 1)), 0, CAST(N'2019-08-23' AS Date))
,(8, N'Dudek', N'Nucleon XX', CAST(24.0 AS Decimal(3, 1)), 150, CAST(N'2021-01-02' AS Date))
,(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 * from wings;
select * from flights;
select * from wingServiceHistory;


Id
Manufacturer
Model
Size
hoursWhenBought
purchaseDate


2
Dudek
Synthesis LT
31.0
45
2017-11-04

3
Dudek
Universal 1.1
28.0
0
2019-08-23

8
Dudek
Nucleon XX
24.0
150
2021-01-02

13
Dudek
Hadron 3
20.0
3
2022-10-16




Id
Date
TakeOffTime
LandingTime
WingId


1
2019-09-02
10:00:00.0000000
12:00:00.0000000
3

2
2019-09-03
09:30:00.0000000
12:30:00.0000000
3

3
2020-05-05
07:00:00.0000000
08:45:00.0000000
3

4
2020-09-28
13:00:00.0000000
15:00:00.0000000
3

5
2021-01-03
17:00:00.0000000
19:00:00.0000000
8

6
2021-01-05
15:30:00.0000000
17:00:00.0000000
8

7
2021-08-25
06:00:00.0000000
08:00:00.0000000
8

8
2021-08-26
07:00:00.0000000
09:30:00.0000000
3

9
2021-09-01
06:00:00.0000000
07:00:00.0000000
8

10
2022-08-10
07:00:00.0000000
09:00:00.0000000
8

11
2022-10-17
15:00:00.0000000
17:00:00.0000000
13

12
2022-10-19
16:00:00.0000000
18:00:00.0000000
8

13
2022-12-21
13:00:00.0000000
15:30:00.0000000
13




wingId
date
servicedBy
comments


3
2020-09-21
Joe Blogs
full trim service

8
2021-08-24
Joe Blogs
full trim service

8
2022-08-03
Joe Blogs
full trim service


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' then workHours else 0 end)
      over(partition by wingId,cicleN order by eventDate) cumService
  ,sum(case when eventType='flight' then workHours else 0 end)over(order by eventDate) totHours
  , id
from LifeCicles
order by wingId,eventDate,id


WingId
EventType
eventDate
TOff
LT
wHours
cicleN
cumHours
cumService
totHours
id


2
purchase
2017-11-04
null
null
45.000000
1
45.000000
45.000000
0.000000
0

3
purchase
2019-08-23
null
null
0.000000
1
0.000000
0.000000
0.000000
0

3
flight
2019-09-02
10:00:00.0000000
12:00:00.0000000
2.000000
1
2.000000
2.000000
2.000000
1

3
flight
2019-09-03
09:30:00.0000000
12:30:00.0000000
3.000000
1
5.000000
5.000000
5.000000
2

3
flight
2020-05-05
07:00:00.0000000
08:45:00.0000000
1.750000
1
6.750000
6.750000
6.750000
3

3
Service
2020-09-21
null
null
0.000000
2
6.750000
0.000000
6.750000
0

3
flight
2020-09-28
13:00:00.0000000
15:00:00.0000000
2.000000
2
8.750000
2.000000
8.750000
4

3
flight
2021-08-26
07:00:00.0000000
09:30:00.0000000
2.500000
2
11.250000
4.500000
16.750000
8

8
purchase
2021-01-02
null
null
150.000000
1
150.000000
150.000000
8.750000
0

8
flight
2021-01-03
17:00:00.0000000
19:00:00.0000000
2.000000
1
152.000000
152.000000
10.750000
5

8
flight
2021-01-05
15:30:00.0000000
17:00:00.0000000
1.500000
1
153.500000
153.500000
12.250000
6

8
Service
2021-08-24
null
null
0.000000
2
153.500000
0.000000
12.250000
0

8
flight
2021-08-25
06:00:00.0000000
08:00:00.0000000
2.000000
2
155.500000
2.000000
14.250000
7

8
flight
2021-09-01
06:00:00.0000000
07:00:00.0000000
1.000000
2
156.500000
3.000000
17.750000
9

8
Service
2022-08-03
null
null
0.000000
3
156.500000
0.000000
17.750000
0

8
flight
2022-08-10
07:00:00.0000000
09:00:00.0000000
2.000000
3
158.500000
2.000000
19.750000
10

8
flight
2022-10-19
16:00:00.0000000
18:00:00.0000000
2.000000
3
160.500000
4.000000
23.750000
12

13
purchase
2022-10-16
null
null
3.000000
1
3.000000
3.000000
19.750000
0

13
flight
2022-10-17
15:00:00.0000000
17:00:00.0000000
2.000000
1
5.000000
5.000000
21.750000
11

13
flight
2022-12-21
13:00:00.0000000
15:30:00.0000000
2.500000
1
7.500000
7.500000
26.250000
13


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


Id
Date
Duration
CumulativeHours


1
2019-09-02
02:00:00.0000000
2.0

2
2019-09-03
03:00:00.0000000
5.0

3
2020-05-05
01:45:00.0000000
6.8

4
2020-09-28
02:00:00.0000000
8.8

5
2021-01-03
02:00:00.0000000
10.8

6
2021-01-05
01:30:00.0000000
12.3

7
2021-08-25
02:00:00.0000000
14.3

8
2021-08-26
02:30:00.0000000
16.8

9
2021-09-01
01:00:00.0000000
17.8

10
2022-08-10
02:00:00.0000000
19.8

11
2022-10-17
02:00:00.0000000
21.8

12
2022-10-19
02:00:00.0000000
23.8

13
2022-12-21
02:30:00.0000000
26.3


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) wsh
 ORDER BY w.id,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

8
2021-08-26
07:00:00.0000000
09:30:00.0000000
3
02:30:00.0000000
16.75
4.50

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

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

12
2022-10-19
16:00:00.0000000
18:00:00.0000000
8
02:00:00.0000000
23.75
7.00

11
2022-10-17
15:00:00.0000000
17:00:00.0000000
13
02:00:00.0000000
21.75
5.00

13
2022-12-21
13:00:00.0000000
15:30:00.0000000
13
02:30:00.0000000
26.25
7.50

Id	Manufacturer	Model	Size	hoursWhenBought	purchaseDate
2	Dudek	Synthesis LT	31.0	45	2017-11-04
3	Dudek	Universal 1.1	28.0	0	2019-08-23
8	Dudek	Nucleon XX	24.0	150	2021-01-02
13	Dudek	Hadron 3	20.0	3	2022-10-16

Id	Date	TakeOffTime	LandingTime	WingId
1	2019-09-02	10:00:00.0000000	12:00:00.0000000	3
2	2019-09-03	09:30:00.0000000	12:30:00.0000000	3
3	2020-05-05	07:00:00.0000000	08:45:00.0000000	3
4	2020-09-28	13:00:00.0000000	15:00:00.0000000	3
5	2021-01-03	17:00:00.0000000	19:00:00.0000000	8
6	2021-01-05	15:30:00.0000000	17:00:00.0000000	8
7	2021-08-25	06:00:00.0000000	08:00:00.0000000	8
8	2021-08-26	07:00:00.0000000	09:30:00.0000000	3
9	2021-09-01	06:00:00.0000000	07:00:00.0000000	8
10	2022-08-10	07:00:00.0000000	09:00:00.0000000	8
11	2022-10-17	15:00:00.0000000	17:00:00.0000000	13
12	2022-10-19	16:00:00.0000000	18:00:00.0000000	8
13	2022-12-21	13:00:00.0000000	15:30:00.0000000	13

wingId	date	servicedBy	comments
3	2020-09-21	Joe Blogs	full trim service
8	2021-08-24	Joe Blogs	full trim service
8	2022-08-03	Joe Blogs	full trim service

WingId	EventType	eventDate	TOff	LT	wHours	cicleN	cumHours	cumService	totHours	id
2	purchase	2017-11-04	null	null	45.000000	1	45.000000	45.000000	0.000000	0
3	purchase	2019-08-23	null	null	0.000000	1	0.000000	0.000000	0.000000	0
3	flight	2019-09-02	10:00:00.0000000	12:00:00.0000000	2.000000	1	2.000000	2.000000	2.000000	1
3	flight	2019-09-03	09:30:00.0000000	12:30:00.0000000	3.000000	1	5.000000	5.000000	5.000000	2
3	flight	2020-05-05	07:00:00.0000000	08:45:00.0000000	1.750000	1	6.750000	6.750000	6.750000	3
3	Service	2020-09-21	null	null	0.000000	2	6.750000	0.000000	6.750000	0
3	flight	2020-09-28	13:00:00.0000000	15:00:00.0000000	2.000000	2	8.750000	2.000000	8.750000	4
3	flight	2021-08-26	07:00:00.0000000	09:30:00.0000000	2.500000	2	11.250000	4.500000	16.750000	8
8	purchase	2021-01-02	null	null	150.000000	1	150.000000	150.000000	8.750000	0
8	flight	2021-01-03	17:00:00.0000000	19:00:00.0000000	2.000000	1	152.000000	152.000000	10.750000	5
8	flight	2021-01-05	15:30:00.0000000	17:00:00.0000000	1.500000	1	153.500000	153.500000	12.250000	6
8	Service	2021-08-24	null	null	0.000000	2	153.500000	0.000000	12.250000	0
8	flight	2021-08-25	06:00:00.0000000	08:00:00.0000000	2.000000	2	155.500000	2.000000	14.250000	7
8	flight	2021-09-01	06:00:00.0000000	07:00:00.0000000	1.000000	2	156.500000	3.000000	17.750000	9
8	Service	2022-08-03	null	null	0.000000	3	156.500000	0.000000	17.750000	0
8	flight	2022-08-10	07:00:00.0000000	09:00:00.0000000	2.000000	3	158.500000	2.000000	19.750000	10
8	flight	2022-10-19	16:00:00.0000000	18:00:00.0000000	2.000000	3	160.500000	4.000000	23.750000	12
13	purchase	2022-10-16	null	null	3.000000	1	3.000000	3.000000	19.750000	0
13	flight	2022-10-17	15:00:00.0000000	17:00:00.0000000	2.000000	1	5.000000	5.000000	21.750000	11
13	flight	2022-12-21	13:00:00.0000000	15:30:00.0000000	2.500000	1	7.500000	7.500000	26.250000	13

Id	Date	Duration	CumulativeHours
1	2019-09-02	02:00:00.0000000	2.0
2	2019-09-03	03:00:00.0000000	5.0
3	2020-05-05	01:45:00.0000000	6.8
4	2020-09-28	02:00:00.0000000	8.8
5	2021-01-03	02:00:00.0000000	10.8
6	2021-01-05	01:30:00.0000000	12.3
7	2021-08-25	02:00:00.0000000	14.3
8	2021-08-26	02:30:00.0000000	16.8
9	2021-09-01	01:00:00.0000000	17.8
10	2022-08-10	02:00:00.0000000	19.8
11	2022-10-17	02:00:00.0000000	21.8
12	2022-10-19	02:00:00.0000000	23.8
13	2022-12-21	02:30:00.0000000	26.3