Count records in overlapsed time

[JF12]

Hi,

I am working on database with an important number of flights at an airport. I need to know how many aircrafts were at the same time at this airport. I'm trying to figure out a query that could something like this, if ti's possible. Here is an exemple:

Data :

Aircraft	Date	ArrivalTime	DepartureTime
A	2022-01-01	08:55	09:01
B	2022-01-01	09:00	09:05
C	2022-01-01	09:00	10:00
D	2022-01-01	09:06	09:08
E	2022-01-01	10:00	12:00

Possible Result :

Date	TimeMin	TimeMax	NbAircraft
2022-01-01	08:55	09:00	1
2022-01-01	09:00	09:01	3
2022-01-01	09:05	09:05	2
2022-01-01	09:06	09:08	1
2022-01-01	09:08	09:10	1
2022-01-01	10:00	12:00	1

Thanks,

[CJ_London]

it is possible, but your example data either doesn't stack up or your description isn't quite complete enough

e.g.
between 8:55 and 9:01 there are three records that fit (A, B and C), you say 1
between 9:06 and 9:08 there are two records that fit (C and D), you say 1
between 10 and 12 there are two records that fit, C is departing, E is arriving, you say 1
you have gaps in your timeline between 9:01 and 9:05 (should be two (B & C))

the basic criteria would be

Arrivaltime<=TimeMax AND DepartureTime>=TimeMin

You have varying periods, presumable based on arrival and departure times. I'm winging this bit since not sure about your data or description (minTime includes arrival and departure times) but for this you would need to get all your times into one column (use a union query if they are in separate columns)

SELECT arrivalTime as ATime FROM myTable
UNION SELECT DepartureTime FROM myTable

note this will remove duplicate arrival or departure times

call this Uqry

then based on Uqry use a cartesian query to find the next highest value as your maxTime

SELECT A.Atime as TimeMin, Min(B.Atime) as TimeMax
FROM Uqry A, UqryB
WHERE B.ATime>=A.Atime
GROUP BY A.Atime

call this Cqry

Finally you can use a third cartesian aggregate query based on the second query and your arrivals/departures table and utilising the basic formula as a criteria

SELECT Date, TimeMin, TimeMax, count(*) as NBAircraft
FROM myTable, Cqry
WHERE Arrivaltime<=TimeMax AND DepartureTime>=TimeMin
GROUP BY Date, TimeMin, TimeMax
HAVING count(*)>0
ORDER BY Date, TimeMin

[JF12]

I made mistakes in my description, sorry.

It seems to get it done, thank very much!!

[CJ_London]

happy to help