Show lines where entry does not occur in next lines

Question

I have a table with statuses and in this fictitious table cars drive to a place and can also be canceled before they arrive. there are more than 4 statuses in my real project .

I'd like to get all Case_Numbers where there was no 'cancelled'.

I have created a DB Fiddle with demo data sets, I hope my problem is understandable and easy to solve. As you can imagine, my way of solving this via a group by didn't work. ANd to check if theres 4 Entries per Case-Number doesnt work either , cause as told before there are more then 4 Statuses

https://dbfiddle.uk/7Yeg4IUQ

The end result should actually throw out the following

Case Number
C001
C002
C004

I would also like to calculate the time difference to the next status. Is it possible to get the following status and the associated timestamp using the case number and the timestamp?

Something like DATEDIFF(SECOND, time, [TIMENEXTSTATUS])

** UPDATE ** Sorry i forgot the link to the dbfiddle . Really Sorry

Also im Using Micrsoft SQL Mangement Studio / TSQL / Windows SQL Server 2012

Answer 1

Try this...

SELECT DISTINCT case_number 
  FROM unit_status
  WHERE case_number NOT IN (SELECT DISTINCT case_number 
                            FROM unit_status 
                            WHERE status = 'cancelled'
                            )

All we do here is get all case_numbers that do have a "cancelled" record, then use this to exclude these cases from the results.