Unfortunately, I have again major problems with a query or with understanding the solution. I've already tested subqueries and an IF EXISTS statement but unfortunately it didn't work

I am looking for the number of events where a vehicle has stopped but has not arrived - i.e. while the drive was canceled

I have a test table that looks like this:

Operations: Different / several vehicles can drive to an event, they have the same event number Im using SQL Server, TSQL

The output should look like this

.

Ive tried already a subquery but i dont know how to get the logic right on this. Or is an if exists statement the best way ?

Im to ashamed to post my trys xD

Thanks a Lot for your Help

CodePudding user response：

This might be an over-simplification, but it appears you are just expecting each "onway" to have a corresponding "arrived" and just what the count of the difference

select 
  Sum(case when status='onway' then 1 end) 
  - Sum(case when status='arrived' then 1 end) CountUnitCancel
from t

CodePudding user response：

You can use LEAD to identify onway rows which are not immediately followed by arrived. Then simply count those rows:

SELECT
  [Count Unit Cancel] = COUNT(IsNotArrived)
FROM (
    SELECT *,
      IsNotArrived = CASE WHEN LEAD(status) OVER (PARTITION BY vehicle, eventnumber
                    ORDER BY cdts) = 'arrived' THEN NULL ELSE 1 END
    FROM YourTable t
) t
WHERE status = 'onway';

db<>fiddle

CodePudding user response：

Easy way with antisemijoin technique:

-- get all vehicles onway (=not yet arrived)
select count(t1.vehicle) as [Vehicle count onway]
from [table] as t1
left join table2 as t2
    on t1.vehicle = t2.vehicle
    and t1.cdts > t2.cdts
    and t2.[status] = 'arrived'
where t1.[status] = 'onway'
    and t2.vehicle is null

The same with exists statement:

-- get all vehicles onway (=not yet arrived)
select count(t1.vehicle) as [Vehicle count onway]
from [table] as t1
where not exists (
    select 2
    from table2 as t2
        on t1.vehicle = t2.vehicle
        and t1.cdts > t2.cdts
        and t2.[status] = 'arrived')
where t1.[status] = 'onway'