SQL - LAG，但跳过特定的行基于值?

Question

我有下面的表格。我要做的是添加一个LAG列，它向我显示了以前的[EndDate]，只有那些状态不是6的条目。6表示不活跃。所以我不想数它们

SPID        ID            Status       StartDate                 EndDate   
29333       102             7         01 jan 2013              1 Jan 2014
29333       202             6         7 Jun 2021               15 Jun 2022
29333       384             6         10 jun 2021              17 jun 2022
29333       532             2         8 oct 2023               10 oct 2025

我使用了一个LAG函数，它可以工作，但不确定我如何修改它来跳过我不需要的行?我的理想输出是这样的。

SPID        ID            Status       StartDate                 EndDate           PrevED
29333       102             7         01 jan 2013              1 Jan 2014        01-Jan-1900  
29333       202             6         7 Jun 2021               15 Jun 2022       1-Jan-2014
29333       384             6         10 jun 2021              17 jun 2022       1-Jan-2014
29333       532             2         8 oct 2023               10 oct 2025       1-Jan-2014

对于实现这一目标的最佳方法有什么建议吗?

Answer 1

正如我在评论中提到的，LAG不太适合这个(除非你在2022年)。相反，您可能希望在FROM:

中使用派生表

SELECT YT.SPID,
YT.ID,
YT.Status,
YT.StartDate,
YT.EndDate,
ISNULL(ED.EndDate,'19000101') AS PrevEdDate
FROM dbo.YourTable YT
OUTER APPLY (SELECT TOP (1) oa.EndDate
FROM dbo.YourTable oa
WHERE oa.EndDate < YT.EndDate
AND oa.Status != 6
ORDER BY oa.EndDate DESC) ED;

如果你是在2022年(预览)，那么你可以使用LAG，这是新的利用IGNORE NULLS功能和CASE表达:

SELECT SPID,
ID,
Status,
StartDate,
EndDate,
LAG(CASE WHEN Status != 6 THEN EndDate END,1,'19000101') IGNORE NULLS OVER (ORDER BY EndDate) AS PrevEd
FROM dbo.YourTable;

Answer 2

你可以这样做:

lag(EndDate) over (order by iif(Status=6,1,0), EndDate)

它将首先按状态排序，将6放在列表的底部，然后使用EndDate查找其他状态的最新值。当然，如果没有任何其他先前的状态，它仍然会选择它。