How to get the next minimum date, which cannot be for 30 days and use as a checkpoint in SQL?

Question

I have a subset of entries that look like this:

ID DATE A 2015-09-01 A 2015-10-03 A 2015-10-10 B 2015-09-01 B 2015-09-10 B 2015-10-03 ... 

For each identifier, the first minimum date is the first index entry. Now I need to exclude cases within 30 days after the index record, and any record with a date exceeding 30 days will become another index record.

For example, for ID A, 2015-09-01 and 2015-10-03 are both index entries and will be saved since they are more than 30 days apart. 2015-10-10 will be dropped because it is within 30 days after the case of the 2nd index.

For ID B, 2015-09-10 will be discarded and will NOT be indexed, because it is within 30 days after the first record of the index. 2015-10-03 will be retained because it exceeds 30 days of the first index entry and will be considered as the second case of the index.

The result should look like this:

 ID DATE A 2015-09-01 A 2015-10-03 B 2015-09-01 B 2015-10-03 

How to do this on SQL Server 2012? There is no limit to how many dates an identifier can have, maybe from 1 to 5 or more. I am pretty easy to use SQL, so any help would be greatly appreciated.

Answer 1

works as in your example, #test is your data table:

 ;with cte1 as ( select ID, Date, row_number()over(partition by ID order by Date) groupID from #test ), cte2 as ( select ID, Date, Date as DateTmp, groupID, 1 as getRow from cte1 where groupID=1 union all select c1.ID, c1.Date, case when datediff(Day, c2.DateTmp, c1.Date) > 30 then c1.Date else c2.DateTmp end as DateTmp, c1.groupID, case when datediff(Day, c2.DateTmp, c1.Date) > 30 then 1 else 0 end as getRow from cte1 c1 inner join cte2 c2 on c2.groupID+1=c1.groupID and c2.ID=c1.ID ) select ID, Date from cte2 where getRow=1 order by ID, Date 

Answer 2

  select * from ( select ID,DATE_, case when DATE_DIFF is null then 1 when date_diff>30 then 1 else 0 end comparison from ( select ID, DATE_ ,DATE_-LAG(DATE_, 1) OVER (PARTITION BY ID ORDER BY DATE_) date_diff from trial ) ) where comparison=1 order by ID,DATE_; 

Tried in an Oracle database. Similar features exist in SQL Server.

I group by the AND column, and based on the DATE field, compare the date in the current field with my previous field. The very first line of this user ID returns null, and the first field is required in our release as the first index. For all other fields, we return 1 when the difference in date compared to the previous field is more than 30.

Delay function in sql transaction

Case function in sql transaction

Answer 3

Try this solution.

Demo example

 with diffs as ( select t1.id,t1.dt strtdt,t2.dt enddt,datediff(dd,t1.dt,t2.dt) daysdiff from t t1 join t t2 on t1.id=t2.id and t1.dt<t2.dt ) , y as ( select id,strtdt,enddt from ( select id,strtdt,enddt,row_number() over(partition by id,strtdt order by daysdiff) as rn from diffs where daysdiff > 30 ) x where rn=1 ) ,z as ( select *,coalesce(lag(enddt) over(partition by id order by strtdt),strtdt) prevend from y) select id,strtdt from z where strtdt=prevend union select id,enddt from z where strtdt=prevend 

Answer 4

Your logic in question is erroneous, in one place you said that you took the first index record, and in the next place you read the immediate record.

This works for immediate entries:

 with cte as ( select *, ROW_NUMBER() over (partition by id order by datee) as rownum from #test ) select *,datediff(day,beforedate,datee) from cte t1 cross apply (Select isnull(max(Datee),t1.datee) as beforedate from cte t2 where t1.id =t2.id and t2.rownum<t1.rownum) b where datediff(day,beforedate,datee)= 0 or datediff(day,beforedate,datee)>=30 

This works for a permanent base record:

 select *,datediff(day,basedate,datee) from #test t1 cross apply (select min(Datee) as basedate from #test t2 where t1.id=t2.id)b where datediff(day,basedate,datee)>=30 or datediff(day,basedate,datee)=0