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