根据日期和SQL Server中的规则识别记录的子集 [英] Identify sub-set of records based on date and rules in SQL Server

问题描述

我有一个像这样的数据集:

I have a dataset that looks like this:

我需要标识已将 Linked 设置为1的行，但仅当按 ToDate 降序排列时，它们才在一起，如图所示.

I need to identify the rows that have Linked set to 1 but ONLY where they are together when sorted by ToDate descending as in the picture.

换句话说，我希望能够识别这些记录(已编辑):

In other words I want to be able to identify these records (EDITED):

这是一个简化的数据集，实际上会有更多的记录...

This is a simplified dataset, in fact there will be many more records...

定义记录是否链接的逻辑是记录的 FromDate 是否在先前日期的 ToDate 的8周内...但这是testData可能并不完美

The logic that defines whether a record is linked is if the FromDate of a record is within 8 weeks of the ToDate of the preceeding date...but this is testData so may not be perfect

请问最好的方法是什么?

What's the best way to do that please?

推荐答案

您可以使用LAG()和LEAD()分析函数:

You can use LAG() and LEAD() analytic functions:

SELECT * FROM (
    SELECT t.*,
           LAG(t.linked,1,0) OVER(ORDER BY t.FromDate DESC) as rnk_1, --Next one
           LEAD(t.linked,1,0) OVER(ORDER BY t.FromDate DESC) as rnk_2, -- Last one,
           LEAD(t.linked,2,0) OVER(ORDER BY t.FromDate DESC) as rnk_3 -- Last two,
    FROM YourTable t) s
WHERE ((s.rnk_1 = 1 OR s.rnk_2 = 1) AND s.linked = 1) OR 
      (s.rnk_2 = 1 and s.rnk_3 = 1 and s.linked = 0)
ORDER BY s.FromDate DESC

这将导致记录具有linked = 1，并且上一个/下一个记录也是1.

This will result in records that have linked = 1 and the previous/next record is also 1.

这篇关于根据日期和SQL Server中的规则识别记录的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！