如何获取不在30天内的下一个最小日期并将其用作SQL中的参考点? [英] How to get next minimum date that is not within 30 days and use as reference point in SQL?

问题描述

我有一条记录的子集，如下所示:

I have a subset of records 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
...

对于每个ID，第一个最小日期是第一个索引记录.现在，我需要排除索引记录后30天内的案例，任何日期大于30天的记录都将成为另一个索引记录.

For each ID the first minimum date is the first index record. Now I need to exclude cases within 30 days of the index record, and any record with a date greater than 30 days becomes another index record.

例如，对于ID A，2015-09-01和2015-10-03都是索引记录，由于相隔30天以上，因此将被保留. 2015年10月10日将被删除，因为它距第二次索引事件的30天之内.

For example, for ID A, 2015-09-01 and 2015-10-03 are both index records and would be retained since they are more than 30 days apart. 2015-10-10 would be dropped because it's within 30 days of the 2nd index case.

对于ID B，由于它位于第一个索引记录的30天内，因此将删除2015-09-10，而不是索引案例. 2015-10-03将被保留，因为它距离第一索引记录的时间超过30天，将被视为第二索引情况.

For ID B, 2015-09-10 would be dropped and would NOT be an index case because it's within 30 days of the 1st index record. 2015-10-03 would be retained because it's greater than 30 days of the 1st index record and would be considered the 2nd index case.

输出应如下所示:

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

如何在SQL Server 2012中执行此操作? ID可以有多少个日期没有限制，可以是1到5个或更多.我对SQL相当了解，因此不胜感激.

How do I do this in SQL server 2012? There's no limit to how many dates an ID can have, could be just 1 to as many as 5 or more. I'm fairly basic with SQL so any help would be greatly appreciated.

推荐答案

工作方式与您的示例类似，#test是包含数据的表:

working like in your example, #test is your table with data:

;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

这篇关于如何获取不在30天内的下一个最小日期并将其用作SQL中的参考点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！