I have a SQL question where I want to evaluate and get the latest work order for a location based on multiple criteria

The table looks something like this

 location   work order         create dt.      status         result  
   1.          123              3/1/22         complete      positive 
   1.          124              3/2/22         incomplete.    null 
   2.          231              2/1/22         cancelled.     null
   2.          232              2/3/22         incomplete.    null 

The requirement is as follows

For each location, find the latest work order based on the following criteria

1. If there are multiple work orders with results, pick the one with the latest date
2. If there are multiple work orders but one with result and one with no result, pick the one with the result - even if it is not latest
3. If there are multiple work orders, but none have result, pick the latest one that is not cancelled
4. If there are multiple work orders, but all are cancelled, pick the latest one

The result would be something like this

location    work order 
  1.           123
  2.           232

Since for location 1, we pick the earlier one, since it has the result And for location 2, we pick the earlier one, since it is not cancelled.

Thanks

CodePudding user response：

Here's the source data

SQL> select * from t;

  LOCATION  WORKORDER CREATED    STATUS               RESULT
---------- ---------- ---------- -------------------- --------------------
         1        123 03/01/2022 complete             positive
         1        124 03/02/2022 incomplete
         2        231 02/01/2022 cancelled
         2        232 02/03/2022 incomplete

We can pick up some additional data on a per location basis

SQL>   select
  2      t.*,
  3      max(created) over ( partition by location) as last_date,
  4      count(result) over ( partition by location) result_count,
  5      max(case when result is not null then created end) over ( partition by location) result_date,
  6      max(case when status != 'cancelled' then created end) over ( partition by location) non_cancelled_date
  7    from t
  8  /

  LOCATION  WORKORDER CREATED    STATUS               RESULT               LAST_DATE  RESULT_COUNT RESULT_DAT NON_CANCEL
---------- ---------- ---------- -------------------- -------------------- ---------- ------------ ---------- ----------
         1        123 03/01/2022 complete             positive             03/02/2022            1 03/01/2022 03/02/2022
         1        124 03/02/2022 incomplete                                03/02/2022            1 03/01/2022 03/02/2022
         2        231 02/01/2022 cancelled                                 02/03/2022            0            02/03/2022
         2        232 02/03/2022 incomplete                                02/03/2022            0            02/03/2022

and use that to apply our rules

SQL> select *
  2  from
  3  (
  4    select
  5      t.*,
  6      max(created) over ( partition by location) as last_date,
  7      count(result) over ( partition by location) result_count,
  8      max(case when result is not null then created end) over ( partition by location) result_date,
  9      max(case when status != 'cancelled' then created end) over ( partition by location) non_cancelled_date
 10    from t
 11  )
 12  where ( result_count > 1 and created = result_date )     -- rule1
 13  or    ( result_count = 1 and created = result_date )     -- rule2
 14  or    ( result_count = 0 and non_cancelled_date = created )    -- rule3
 15  or    ( result_count = 0 and non_cancelled_date is null and created = last_date ) -- rule4
 16  /

  LOCATION  WORKORDER CREATED    STATUS               RESULT               LAST_DATE  RESULT_COUNT RESULT_DAT NON_CANCEL
---------- ---------- ---------- -------------------- -------------------- ---------- ------------ ---------- ----------
         1        123 03/01/2022 complete             positive             03/02/2022            1 03/01/2022 03/02/2022
         2        232 02/03/2022 incomplete                                02/03/2022            0            02/03/2022

If you're unfamiliar with these "OVER" functions, here's my tutorial series on them https://www.youtube.com/watch?v=0cjxYMxa1e4&list=PLJMaoEWvHwFIUwMrF4HLnRksF0H8DHGtt

CodePudding user response：

I think you are looking at something like:

 select top 1 -- with ties ??
             location, 
             [work order], 
            case result not null then 1  
            case status <> 'cancelled' then 2
            else 3 end as StatusOrder,
            [create dt]
    from WorkOrders
    order by row_number() 
             over (partition by location 
                   order by StatusOrder, [create dt] desc)