SQL Server:从该ID的每个状态中获取min(lastupdatedate)

Question

我想找到持续时间。我想为每个学生ID获取该状态的最小值(上次更新日期(，并从该状态的下一个分钟(上次更新日期(中减去。如果没有下一个状态可用，那么我们必须考虑 getdate((。如果我对给定的学生 ID 只有 1 个状态，那么 min(lastupdatedate(-getdate((

CREATE TABLE mytable
(
id INT IDENTITY(1,1),
studentid int NOT NULL,
status VARCHAR(7),
createddate DATE,
lastupdatedate DATE,
durationdays INT NULL
);
INSERT INTO mytable(studentid, status, createddate, lastupdatedate) 
VALUES
(1,'New','2/15/2019','2/15/2019')
,(1,'Open','2/15/2019','2/25/2019')
,(1,'Sub','2/15/2019','3/17/2019')
,(1,'PS','2/15/2019','3/27/2019')
,(1,'PI','2/15/2019','5/11/2019')
,(1,'PR','2/15/2019','5/15/2019')
,(1,'PI','2/15/2019','5/27/2019')
,(1,'PC','2/15/2019','8/25/2019')
,(1,'PI','2/15/2019','9/4/2019')
,(1,'PI','2/15/2019','10/24/2019')
,(1,'PC','2/15/2019','11/5/2019')
,(1,'RC','2/15/2019','11/16/2019') ;

输出

studentid   status    createddate lastupdatedate  duration_days
--------------------------------------------------------------------
1         New      2019-02-15  2019-02-15          10
1         Open     2019-02-15  2019-02-25          20
1         Sub      2019-02-15  2019-03-17          10
1         PS       2019-02-15  2019-03-27          45
1         PI       2019-02-15  2019-05-11           4
1         PR       2019-02-15  2019-05-15          12
1         PI       2019-02-15  2019-05-27          90
1         PC       2019-02-15  2019-08-25          10
1         PI       2019-02-15  2019-09-04          50
1         PC       2019-02-15  2019-11-05          11
1         RC       2019-02-15  2019-11-16          34

Answer 1

逻辑的说明与所需输出中的示例数据不一致。要获得示例数据，您只需将LEAD函数应用于现有数据。

SELECT
*,
DATEDIFF
(
DAY,
lastupdatedate, 
LEAD(lastupdatedate,1,GETDATE()) OVER (PARTITION BY studentid ORDER BY lastupdatedate)
) as durationdays
FROM 
@mytable

结果：

+----+-----------+--------+-------------+----------------+--------------+
| id | studentid | status | createddate | lastupdatedate | durationdays |
+----+-----------+--------+-------------+----------------+--------------+
|  1 |         1 | New    | 2019-02-15  | 2019-02-15     |           10 |
|  2 |         1 | Open   | 2019-02-15  | 2019-02-25     |           20 |
|  3 |         1 | Sub    | 2019-02-15  | 2019-03-17     |           10 |
|  4 |         1 | PS     | 2019-02-15  | 2019-03-27     |           45 |
|  5 |         1 | PI     | 2019-02-15  | 2019-05-11     |            4 |
|  6 |         1 | PR     | 2019-02-15  | 2019-05-15     |           12 |
|  7 |         1 | PI     | 2019-02-15  | 2019-05-27     |           90 |
|  8 |         1 | PC     | 2019-02-15  | 2019-08-25     |           10 |
|  9 |         1 | PI     | 2019-02-15  | 2019-09-04     |           50 |
| 10 |         1 | PI     | 2019-02-15  | 2019-10-24     |           12 |
| 11 |         1 | PC     | 2019-02-15  | 2019-11-05     |           11 |
| 12 |         1 | RC     | 2019-02-15  | 2019-11-16     |           37 |
+----+-----------+--------+-------------+----------------+--------------+

这显示了状态发生任何变化的时间，尽管您的描述涉及每个状态中的更改。

Answer 2

但是，此查询执行您上面要求的操作，当在非常奇怪的日期时间内没有延迟日期可以从结果中提取时减去 GETDATE((。

DECLARE @GetDate DATETIME ='02/05/2019'
SELECT 
id,studentid,status,createddate,
lastupdatedate,
NextLastModifiedDateByStudentStatus = LEAD(lastupdatedate) OVER(PARTITION BY studentid,status ORDER BY lastupdatedate),
derivednextupdatedate= ISNULL(LEAD(lastupdatedate) OVER(PARTITION BY studentid,status ORDER BY lastupdatedate),@GetDate),
daysapart =  ABS(DATEDIFF(DAY,ISNULL(LEAD(lastupdatedate) OVER(PARTITION BY studentid,status ORDER BY lastupdatedate),@GetDate),lastupdatedate))
FROM 
@mytable
ORDER BY 
studentId, status, lastupdatedate