Main page

## Grouping of time intervals

P. Voynov

This work is the conversion of Itsik Ben-Gun article, which is at the address http://www.osp.ru/win2000/sql/master/25mstr09.htm ,
in appliance to the educational base “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.

View vw_trip

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.

View vw_dt_start

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
)

Illustrations:

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.

View vw_dt_end

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.

View vw_result

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)

Main page

 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books