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
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