CODEWITHSUNDEEP
sql-servert-sqlgaps-and-islands
Ramdeo angh
Ramdeo angh

Reputation: 179

Find Non Consecutive date in SQL Server

I want to find the missing NON-consecutive dates between two consecutive date.

I am posting my SQL query and temp tables to find out the results.

But I am not getting the proper results

Here is my SQL Query

  drop table #temp
  create table #temp(an varchar(20),dt date)
  insert into #temp   
  select    '2133783715'    ,   '2016-10-16'    union all
  select    '5107537880'    ,   '2016-10-15'    union all
  select    '6619324250'    ,   '2016-10-15'    union all
  select    '7146586717'    ,   '2016-10-15'    union all
  select    '7472381321'    ,   '2016-10-12'    union all
  select    '7472381321'    ,   '2016-10-13'    union all
  select    '7472381321'    ,   '2016-10-14'    union all
  select    '7472381321'    ,   '2016-10-24'    union all
  select    '8186056340'    ,   '2016-10-15'    union all
  select    '9099457123'    ,   '2016-10-12'    union all
  select    '9099457123'    ,   '2016-10-13'    union all
  select    '9099457123'    ,   '2016-10-14'    union all
  select    '9099457123'    ,   '2016-10-23'    union all
  select    '9099457123'    ,   '2016-11-01'    union all
  select    '9099457123'    ,   '2016-11-02'    union all
  select    '9099457123'    ,   '2016-11-03'    union all
  select    '9165074784'    ,   '2016-10-16'


drop table #final
SELECT an,MIN(dt) AS MinDate,MAX(dt) AS MaxDate, COUNT(*) AS ConsecutiveUsage
  --DateDiff(Day,LAG(MAX(dt)) OVER (partition by an ORDER BY an),MAX(dt)) nonusageDate
 into #final
  FROM(
  SELECT an,dt,
   DATEDIFF(D, ROW_NUMBER() OVER(partition by an ORDER BY dt),dt) AS Diff
   FROM  #temp c 
)P
GROUP BY an,diff 

select * from #final order by 1

an             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 I want results of non-usage date.

I want to get those AN which has not been used continuously since 10 days.

So here output should be like this:-

  an           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 I just want to find out only consecutive not used dates count and their min and max dates .

Upvotes: 1

Views: 1512

Answers (4)

Esperento57
Esperento57

Reputation: 17492

try this :

  with ranked as (
  select f1.*, 
  ROW_NUMBER() over(partition by an order by dt) rang
  from #temp f1
  where exists
  (select * from #temp f2
   where f1.an=f2.an and datediff( day, f2.dt, f1.dt) >1
  )
  )
  select an, minusagesdate, maxusagesdate,  ConsecutiveNotUseddays
  from (
  select f1.*, 
  DATEADD(DAY,1, (select f2.dt from ranked f2 where f1.an=f2.an and f2.rang+1=f1.rang)) minusagesdate   ,
  DATEADD(DAY,-1, f1.dt) maxusagesdate  , 
  datediff( day, (select f2.dt from ranked f2 where f1.an=f2.an and f2.rang+1=f1.rang), f1.dt) - 1 ConsecutiveNotUseddays
  from ranked f1 
  ) tmp
  where tmp.ConsecutiveNotUseddays>0

or like this

  with ranked as (
  select f1.*, 
  ROW_NUMBER() over(partition by an order by dt) rang
  from #temp f1
  where exists
  (select * from #temp f2
  where f1.an=f2.an and 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
  from ranked f1 
    outer apply
    (
    select top 1 f2.dt as dtbefore from ranked f2 
    where f1.an=f2.an and f2.rang+1=f1.rang
    ) f3
  where datediff( day, f3.dtbefore, f1.dt) - 1>0

Upvotes: 1

dean
dean

Reputation: 10098

On any newer version of SQL Server this should be easy:

with x as (
    select *, lag(dt) over(partition by an order by dt) dt_lag
    from #temp
)
select *, datediff(day, dt_lag, dt) 
from x 
where datediff(day, dt_lag, dt) >= 10

Upvotes: 0

gofr1
gofr1

Reputation: 15997

As I understood you need this:

;WITH cte AS (
    SELECT  an,
            dt,
            ROW_NUMBER() OVER (PARTITION BY an ORDER BY dt) as rn
    FROM #temp
)

SELECT  c1.an,
        c1.dt MinDate,
        c2.dt MaxDate,
        DATEDIFF(day,c1.dt,c2.dt) as ConsecutiveNotUseddays
FROM cte c1
INNER JOIN cte c2
    ON c1.an = c2.an AND c1.rn = c2.rn-1
WHERE DATEDIFF(day,c1.dt,c2.dt) >= 10

Output:

an          MinDate     MaxDate     ConsecutiveNotUseddays
7472381321  2016-10-14  2016-10-24  10

For 9099457123 I got two rows with 9 in ConsecutiveNotUseddays. You can check results removing WHERE statement.

Upvotes: 0

BeanFrog
BeanFrog

Reputation: 2315

It looks like you're trying to count the number of days not used between the mindate and the maxdate for each an. If that's the case, then this should do the trick:

select an, min(dt) as min_dt, max(dt) as max_dt
     , count(distinct dt) as daysused --this counts each day used, but only once
     , datediff(day,min(dt),max(dt)) as totaldays --this is the total number of days between min and max date
     , datediff(day,min(dt),max(dt)) - count(distinct dt) as daysnotused
            --This takes total days - used days to give non-used days
from #temp c 
group by an
having datediff(day,min(dt),max(dt)) - count(distinct dt) >= 10

Upvotes: 0

Related Questions