具有多个变量的sql日期差异

Question

我正在尝试获取生效状态0与最近状态1 之间的日期差异天数

下面的代码产生以下结果

SELECT * FROM
(SELECT FILEKEY, STATUS, EFFDATE  FROM ASTATUSHIST
UNION
SELECT FILEKEY, ASTATUS, ASTATUSEFFDATE FROM USERS ) A
ORDER BY 1, 3 DESC
130 0   2019-10-25 00:00:00.000
130 0   2017-03-01 00:00:00.000
130 0   2017-01-01 00:00:00.000
130 1   2005-02-01 00:00:00.000
130 0   2001-03-03 00:00:00.000
130 0   2000-01-30 00:00:00.000
130 0   2000-01-01 00:00:00.000

这段代码将两个表组合在一起，以获得给定用户的完整历史记录。理想情况下，我可以生产出这样的东西：

130    4352

或

125    null

其中null是没有状态1的文件密钥，或者是有状态1但没有以下状态0的文件密钥

感谢

Answer 1

在所有支持的SQL Server版本中，您都可以使用窗口函数：

with t as (
<your query here>
)
select t.*,
datediff(day, date, next_date) as days_diff
from (select t.*,
row_number() over (partition by filekey, status order by date desc) as seqnum,
lead(date) over (partition by filekey order by date) as next_date
from t
) t
where seqnum = 1;