SQL Server SQL Query to find the cancel status rows

wdebmtf2 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(120)

I need to write a select which groups by common_id and trigger and the status of that trigger is only cancel. Or cancel time precedes any other status time.

table_id    common_id       trigger     status      Time
    11          123         sale        cancel      12/09/2023 11:40       
    12          123         sale        started     12/09/2023 01:40    
            - no row included as cancel time is older than started time
    13          123         purchase    started     11/09/2023 05:43    
    14          123         purchase    finalised   12/09/2023 06:40    
    15          123         purchase    complete    12/09/2023 06:40    
            - no row as nothing has cancel status
    16          345         on hold     started     13/09/2023 06:40    
    17          345         on hold     cancel      14/09/2023 06:40    
    18          345         sale        started     10/09/2023 02:56    
    19          345         purchase    cancel      11/09/2023 06:55    
            - 16,17,19 to be included in select
    20          567         sale        cancel      12/09/2023 08:55    
    21          567         purchase    started     12/09/2023 10:55    
            - 20 to be included in select
    22          789         purchase    cancel      15/09/2023 06:05    
    23          789         sale        cancel      15/09/2023 12:50    
    24          789         on hold     started     15/09/2023 11:55    
            - 22,23 to be included in select

Resulting table from Select should be :

table_id    common_id       trigger     status      Time    
    16          345         on hold     started     13/09/2023 06:40    
    17          345         on hold     cancel      14/09/2023 06:40    
    19          345         purchase    cancel      11/09/2023 06:55    
    20          567         sale        cancel      12/09/2023 08:55    
    22          789         purchase    cancel      15/09/2023 06:05    
    23          789         sale        cancel      15/09/2023 12:50

sql-server

来源：https://stackoverflow.com/questions/77131330/sql-query-to-find-the-cancel-status-rows

1条答案

按热度按时间

iugsix8n1#

Try a query that groups the data by common_id and trigger , and then selects the rows where the status is "cancel" and the time is the maximum time for that group.

Here's a potential solution:

SELECT t1.table_id, t1.common_id, t1.trigger, t1.status, t1.Time
FROM your_table t1
INNER JOIN (
    SELECT common_id, trigger, MAX(Time) AS max_time
    FROM your_table
    WHERE status = 'cancel'
    GROUP BY common_id, trigger
) t2
ON t1.common_id = t2.common_id
AND t1.trigger = t2.trigger
AND t1.Time = t2.max_time

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL Query to find the cancel status rows

1条答案

相关问题

热门标签

最新问答