Saturday September 23rd 2017

Categories

Insider

Archives

Using subqueries to find gaps in sequence

Using subqueries to find gaps in sequence source Microsoft Database development self-Paced Training Kit
Tobias Thernstrom,
Ann Weber,
Mike Hotek and GrandMasters
sequencegaps
Create Table #orderdates
(customerid int not null,
orderdate DateTime not null);
Declare @startdate datetime,
@enddate datetime,
@customerid int
set @customerid =1
while @customerid<100 begin select @startdate = 'Jan 01,2007', @enddate=dateadd(yy,1,@startdate); with dates as (select @startdate as begindate union all select DATEADD(dd,1,begindate) from dates where begindate –delete some of the rows to produce
delete #orderdates
where datediff(dd,0,orderdate)%11=0;
—produce a couple of multi-day gaps
delete from #orderdates
where orderdate in (‘1/4/2007′,’2/17/2007’)
and customerid%3=0
select * from #orderdates
—-findgap
select customerid,startgap,endgap,
DATEDIFF(dd,startgap,endgap)+1 as numberMissingdays
from
(select t1.customerid,t1.orderdate as startgap,MIN(t2.orderdate) as endgap
from
(select customerid,DATEADD(dd,1,orderdate)as orderdate from
#orderdates tbl1
where not exists (select * from #orderdates tbl2
where DATEDIFF(dd,tbl1.orderdate,tbl2.orderdate)=2
and tbl1.customerid=tbl2.customerid)
and orderdate<>(select MAX(orderdate) from #orderdates)) t1

inner join
(select customerid,DATEADD(dd,-1,orderdate)as orderdate
from #orderdates tbl1

where not exists(select * from #orderdates tbl2

where DATEDIFF(dd,tbl2.orderdate,tbl1.orderdate)=2
and tbl1.customerid=tbl2.customerid)

and orderdate<>(select MIN(orderdate) from #orderdates))t2

on t1.orderdate<=t2.orderdate and t1.customerid=t2.customerid group by t1.customerid,t1.orderdate) a order by customerid,startgap;