SQL Query Self Join / 2x Minimum

Question

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

Please use standard spelling & punctuation & reasonable formatting per the page edit help. Debug questions require a minimal reproducible example. How to Ask Help center — philipxy
– philipxy, Commented Oct 12, 2022 at 10:00

NickW · Accepted Answer · 2022-10-12 10:44:29Z

0

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

answered Oct 12, 2022 at 10:44

NickW

10k2 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NickW Over a year ago

No worries - if you could accept my answer that would be great. Thanks

klueting Over a year ago

Can you pls tell me how to accept?! Thx in advance

NickW Over a year ago

There should be a tick symbol next to the answer which you can click

Collectives™ on Stack Overflow

SQL Query Self Join / 2x Minimum

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related