如何获得不在 30 天内的下一个最小日期并用作 SQL 中的参考点?

2021-12-26 00:00:00 loops while-loop sql sql-server

我有一个看起来像这样的记录子集:

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，2015-09-10 将被删除并且不会成为索引案例，因为它在第一个索引记录的 30 天内.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

相关文章