Grouping of time intervals

This work is the conversion of Itsik Ben-Gun article in appliance to the educational database “Aeroflot”.

Only one of the types of solution, which are considered in the said article, will be applied here.

Let’s consider the following task:

For every company to determine time intervals, when there is any plane of the company in the air.
  Extra restrictions:
    - A flight is carried on in case at least one ticket would have been sold;
    - It’s considered that during the take-off and the landing a plane is in the air.

We’ll need the following information for work: a company identifier, date-time of take-off and landing of the plane.

Note: for briefness and convenience of apprehension, I’ll create views, which will be used during the solving of the task. We should mention that thereafter it will be easy to “develop” the got solution in one “select”.

So.

vw_trip view

CREATE VIEW dbo.vw_trip
as
SELECT DISTINCT id_comp,
dt_out = date + time_out,
dt_in = date + time_in + CASE WHEN time_out > time_in THEN 1 ELSE 0 END
FROM trip t JOIN
pass_in_trip pt ON t.trip_no = pt.trip_no

The main difficulty of the solution lies in the fact that we don’t know how many flights can organize a continuous time interval, during which there is at least one plane of the company in the air(in prospect an ”interval”).

To find the beginning of the “interval” we will use such a fact: the beginning of the “interval” coincides with the plane’s take-off, if at this moment there is no other plane of the company in the air. Or, in other words, the beginning of the “interval” coincides with the plane’s take-off, if there is no other flights of the company, which had already started, but have not landed yet.

vw_dt_start view

CREATE VIEW dbo.vw_dt_start
AS
SELECT DISTINCT id_comp, dt_out
FROM vw_trip t
WHERE NOT EXISTS( SELECT 1
FROM vw_trip
WHERE id_comp = t.id_comp AND
dt_out < t.dt_out AND
dt_in > = t.dt_out
)

Explanation:

dt_out < t.dt_out - the strict inequality, not to be checked the considered flight;

dt_in

= t.dt_out - the non-strict inequality; we also check the situation, when one plane takes off, and the other one is landing at this time (“interval” isn’t broken);

DISTINCT - we remove the duplicating of the records in case of simultaneous take-off of two or more planes.

The time of the “interval’s” ending is found by the analogous way.

vw_dt_end view

CREATE VIEW dbo.vw_dt_end
AS
SELECT DISTINCT id_comp, dt_in
FROM vw_trip t
WHERE NOT EXISTS(SELECT 1
FROM vw_trip
WHERE id_comp = t.id_comp AND
dt_out <= t.dt_in AND
dt_in > t.dt_in
)

Now to connect the times of the beginning and the ending of the “intervals” is rest. As intervals don’t intersect, we may confirm that the time of the “interval’s” ending – is a minimal time of all vw_dt_end.dt_in that exceed the considered time of the “interval’s” beginning.

vw_result view

CREATE VIEW dbo.vw_result
AS
SELECT name_comp = (SELECT [name]
FROM company
WHERE id_comp = vw_dt_start.id_comp
), 
vw_dt_start.dt_out, dt_in = MIN(vw_dt_end.dt_in)
FROM vw_dt_start JOIN
vw_dt_end on vw_dt_start.id_comp = vw_dt_end.id_comp AND
vw_dt_start.dt _out < vw_dt_end.dt_in
GROUP BY vw_dt_start.id_comp, vw_dt_start.dt_out

The reader is offered to solve this problem without use of views (i.e. to construct the solution in the form of single SELECT statement).