sqlsubqueryself-joinminimumdate-difference

SQL Query Self Join / 2x Minimum


I want to know the time difference when first time Status HD05/SD05/HD04 was set and the first time HD10/SD10 was set.

heres a part of the table (the status can be set twice or more ... i want the minimum)

Ticket_Nummer Status Datum
HD22-26543 HD04 2022-09-14 16:29:58.480
HD22-26543 HD04 2022-09-14 16:29:59.480
HD22-26543 HD10 2022-09-15 07:08:21.777
HD22-26543 HD10 2022-09-28 15:12:09.200
SELECT DATEDIFF(MINUTE, FTa.Date, FTb.Date) AS Difference,FTa.Date,FTb.Date
FROM [dbo].[FT] FTa
INNER JOIN [dbo].[FT] FTb
ON FTa.Ticket_No = FTb.Ticket_No
WHERE FTa.Date IN (
    SELECT min(x.Date)
    FROM FT x
    WHERE x.Ticket_No = FTa.Ticket_No AND FTa.Status in ('HD04','HD05','SD05')
    ) 
AND FTb.Status IN ('HD10','SD10') AND DATEDIFF(MINUTE,FTa.Date, FTb.Date)>30

If i use the following sub select in where condition:

SELECT min(x.Date)
FROM FT x
WHERE x.Ticket_No = FTb.Ticket_No AND FTb.Status IN ('HD10','SD10'))

i get no results but this should do the same as above with HD05/SD05/HD04

IF i do it without the minimum select from above i get double Results with both HD10/SD10 Status


Solution

  • Something like this should work:

    with data1 as (
    SELECT Ticket_Nummer, MIN(Datum) AS min_datum
    FROM[dbo].[FT]
    WHERE status IN ('HD05', 'SD05', 'HD04')
    ),
    data2 as (
    SELECT Ticket_Nummer, MIN(Datum) AS min_datum
    FROM[dbo].[FT]
    WHERE status IN ('HD10', 'SD10')
    )
    SELECT a.Ticket_Nummer, (b.min_datum - a.min_datum) as time_diff
    from data1 a
    inner join data2 b on a.Ticket_Nummer = b.Ticket_Nummer