tsql - Find Non Consecutive date in SQL Server -

i want find missing non-consecutive dates between 2 consecutive date.

i posting sql query , temp tables find out results.

but not getting proper results

here sql query

  drop table #temp   create table #temp(an varchar(20),dt date)   insert #temp      select    '2133783715'    ,   '2016-10-16'    union   select    '5107537880'    ,   '2016-10-15'    union   select    '6619324250'    ,   '2016-10-15'    union   select    '7146586717'    ,   '2016-10-15'    union   select    '7472381321'    ,   '2016-10-12'    union   select    '7472381321'    ,   '2016-10-13'    union   select    '7472381321'    ,   '2016-10-14'    union   select    '7472381321'    ,   '2016-10-24'    union   select    '8186056340'    ,   '2016-10-15'    union   select    '9099457123'    ,   '2016-10-12'    union   select    '9099457123'    ,   '2016-10-13'    union   select    '9099457123'    ,   '2016-10-14'    union   select    '9099457123'    ,   '2016-10-23'    union   select    '9099457123'    ,   '2016-11-01'    union   select    '9099457123'    ,   '2016-11-02'    union   select    '9099457123'    ,   '2016-11-03'    union   select    '9165074784'    ,   '2016-10-16'   drop table #final select an,min(dt) mindate,max(dt) maxdate, count(*) consecutiveusage   --datediff(day,lag(max(dt)) on (partition order an),max(dt)) nonusagedate  #final   from(   select an,dt,    datediff(d, row_number() over(partition order dt),dt) diff     #temp c  )p group an,diff   select * #final order 1              mindate      maxdate     consecutiveusage     2133783715     2016-10-16   2016-10-16  1     5107537880     2016-10-15   2016-10-15  1     6619324250     2016-10-15   2016-10-15  1     7146586717     2016-10-15   2016-10-15  1     7472381321     2016-10-12   2016-10-14  3     7472381321     2016-10-24   2016-10-24  1    7472381321     2016-10-27   2016-10-28  1  8186056340     2016-10-15   2016-10-15  1     9099457123     2016-10-12   2016-10-14  3     9099457123     2016-10-23   2016-10-23  1     9165074784     2016-10-16   2016-10-16  1     

but want results of non-usage date.

i want has not been used continuously since 10 days.

so here output should this:-

            minusagesdate  maxusagedate        consecutivenotuseddays        7472381321   2016-10-15     2016-10-23           9   7472381321   2016-10-25     2016-10-26           2   9099457123   2016-10-15     2016-10-22           8 

so want find out consecutive not used dates count , min , max dates .

try :

  ranked (   select f1.*,    row_number() over(partition order dt) rang   #temp f1   exists   (select * #temp f2    f1.an=f2.an , datediff( day, f2.dt, f1.dt) >1   )   )   select an, minusagesdate, maxusagesdate,  consecutivenotuseddays   (   select f1.*,    dateadd(day,1, (select f2.dt ranked f2 f1.an=f2.an , f2.rang+1=f1.rang)) minusagesdate   ,   dateadd(day,-1, f1.dt) maxusagesdate  ,    datediff( day, (select f2.dt ranked f2 f1.an=f2.an , f2.rang+1=f1.rang), f1.dt) - 1 consecutivenotuseddays   ranked f1    ) tmp   tmp.consecutivenotuseddays>0 

or this

  ranked (   select f1.*,    row_number() over(partition order dt) rang   #temp f1   exists   (select * #temp f2   f1.an=f2.an , datediff( day, f2.dt, f1.dt) >1   )   )   select f1.an,    dateadd(day,1, f3.dtbefore) minusagesdate   ,   dateadd(day,-1, f1.dt) maxusagesdate  ,    datediff( day, f3.dtbefore, f1.dt) - 1 consecutivenotuseddays   ranked f1      outer apply     (     select top 1 f2.dt dtbefore ranked f2      f1.an=f2.an , f2.rang+1=f1.rang     ) f3   datediff( day, f3.dtbefore, f1.dt) - 1>0