如何在T—SQL中找到值更改之前的日期

发布于04月05日

所以我有一个下面的表格:

CreatedAt                    ID    NewStatus    OldStatus
2023-03-08 13:34:57.0000000 1645    Draft        NULL
2023-03-22 19:58:51.0000000 1645    Active       Draft
2023-04-29 05:59:02.0000000 1645    Closed      Active
2023-05-08 14:50:29.0000000 1645    Awarded     Closed
2023-05-08 14:53:34.0000000 1645    ConfirmationStarted Awarded
2023-05-08 17:53:55.0000000 1645    ConfirmationDone    ConfirmationStarted

我想获取这个ID在"ObservationStarted"之前得到"Closed"的日期，所以在这种情况下，我会得到以下结果

ID    xdate
1645  2023-04-29 05:59:02.0000000

我写了一个T—SQL查询:

WITH StatusChanges AS (
    SELECT 
        ID,
        newstatus,
        oldstatus,
        CreatedAt,
        LEAD(newstatus) OVER (PARTITION BY IDORDER BY CreatedAt) AS next_status

    FROM 
        tableA 
        
)
SELECT 
    ID,
   MAX(CreatedAt) AS xdate
FROM 
    StatusChanges
WHERE 
    next_status = 'Confirmation Started' and OldStatus = 'Closed'
GROUP BY 
    ID;

此查询适用于大多数情况，例如此ID

CreatedAt                      ID     NewStatus     OldStatus
2022-05-25 23:17:44.0000000 147 Active  Closed
2022-05-28 05:59:02.0000000 147 Closed  Active
2022-05-30 20:48:53.0000000 147 Active  Closed
2022-06-18 05:59:01.0000000 147 Closed  Active
2022-06-21 20:09:48.0000000 147 Active  Closed
2022-06-25 05:59:01.0000000 147 Closed  Active
2022-07-13 00:02:47.0000000 147 ConfirmationStarted Closed
2022-07-15 15:33:30.0000000 147 ConfirmationDone    ConfirmationStarted

我想要的日期:2022-06-25 05:59:01.0000000

1645 ID怎么办？

SELECT ID, MAX(CASE WHEN NewStatus = 'Closed' THEN CreatedAt END) AS xdate FROM mytable WHERE NewStatus in ('Closed','ConfirmationStarted') GROUP BY ID HAVING MAX(CASE WHEN NewStatus = 'Closed' THEN CreatedAt END) < MAX(CASE WHEN NewStatus = 'ConfirmationStarted' THEN CreatedAt END)

CREATE TABLE mytable ( CreatedAt datetime, ID int, NewStatus varchar(30), OldStatus varchar(30) ); INSERT INTO mytable VALUES ('2023-03-08 13:34:57', 1645, 'Draft', NULL), ('2023-03-22 19:58:51', 1645, 'Active', 'Draft'), ('2023-04-29 05:59:02', 1645, 'Closed' , 'Active'), ('2023-05-08 14:50:29', 1645, 'Awarded', 'Closed'), ('2023-05-08 14:53:34', 1645, 'ConfirmationStarted', 'Awarded'), ('2023-05-08 17:53:55', 1645, 'ConfirmationDone', 'ConfirmationStarted'), ('2022-05-25 23:17:44', 147, 'Active', 'Closed'), ('2022-05-28 05:59:02', 147, 'Closed', 'Active'), ('2022-05-30 20:48:53', 147, 'Active', 'Closed'), ('2022-06-18 05:59:01', 147, 'Closed', 'Active'), ('2022-06-21 20:09:48', 147, 'Active', 'Closed'), ('2022-06-25 05:59:01', 147, 'Closed', 'Active'), ('2022-07-13 00:02:47', 147, 'ConfirmationStarted', 'Closed'), ('2022-07-15 15:33:30', 147, 'ConfirmationDone', 'ConfirmationStarted');

如何在T—SQL中找到值更改之前的日期

推荐答案

Sql相关问答推荐

如何查询一个名称是根据PL/pgSQL函数结果构建的表？

如何连接第二个表并将其内容输入到第一个表的单个字段中？

使用sede获取不一定有两个不同标签的所有问题

部分匹配表中元素的MariaDB查询查找结果

如何在postgres函数中插入后返回布尔值？

按连续相等值分组排序

在WHERE EXISTS子查询中，列返回是否重要？

SQL仅返回第一个字母在A-Z之间的值

在 Postgres 中将结果按几十年划分

如何创建snowflake表(动态查找数据类型)并从阶段加载(AWS S3)？

确定小数中使用的精度位数

如何在 SQL Server 中解决这个复杂的窗口查询？

Postgresql 生成器列导致语法错误

Postgresql 具有相似行为和模式行为的问题

如何为给定的股票数据集计算利润/亏损，确保先卖出先买入的股票

使用ALTER TABLE无法删除列

oracle中多行的跨日期范围的交集

如何在一个存储过程中创建全局临时表，并在另一个存储过程中使用它

如何找到特定时间间隔内的最大和最小日期？

pyspark 将列转换为行