Allen's Interval Algebra Operations In Sql

I've been struggling to solve a few tricky problems in SQL where I need to infer asset utilisation from event intervals, and have just learned about Allen's Interval Algebra, which

Solution 1:

Here is a SQLFiddle demo First of all create temp tables to simplify queries though you can put these creation queries into final queries and do it without temp tables:

createtable t asselect*from
(
selectnull s ,"start"-1as e  from data
unionallselect "start" s,null e  from data
unionallselect "end"+1 s ,null e  from data
unionallselectnull s ,"end" e  from data
) d whereexists (select "start" 
                  from data where d.s between data."start" and data."end"
                               or d.e between data."start" and data."end"
                                );
--Operation 1 - Disjoined Result   createtable t1 asselect s,e,e-s+1 width from
(
selectdistinct s,(selectmin(e) from t where t.e>=t1.s) e from t t1
) t2 where t2.s isnotnulland t2.e isnotnull;

--Operation 2 - Reduced Resultcreatetable t2 asselect s,e,e-s+1 width from
(
select s,(selectmin(d2.e) from t1 d2 where d2.s>=d.s andnotexists
          (select s from t1 where t1.s=d2.e+1) ) e
from
t1 d wherenotexists(select s from t1 where t1.e=d.s-1) 
) t2;

--Temp table for Operation 3 - Gapscreatetable t3 asselectnull s, s-1 e from t2
unionallselect e+1 s, null e from t2;

Now here are queries:

--Operation 1 - Disjoined Resultselect*from t1 orderby s;

--Operation 2 - Reduced Resultselect*from t2 orderby s;

--Operation 3 - Gapsselect s,e,e-s+1 width 
from
(
select s,(selectmin(e) from t3 where t3.e>=d.s) e from t3 d
) t4 where s isnotnulland e isnotnullorderby s;