How to run Row_Number() with filtering

Question

I have a table with multiple rows for each 'Case Number'. I want to pick one row for each Case Number and join this back to another table maintaining a one-to-one relationship.

The conditions to pick this row are :

1) First of all filter out all rows for each Case Number that have Stage = Cancelled

2) If you find Stage = 'In Progress' or 'Paused', pick that row. (Only one of these two can be present for a Case Number)

3) If not (2), then pick Stage = 'Completed' but for the latest 'Stop Time'. (This is where I thought we might have to use ROW_NUMBER())

I've already created a query to push in row numbers and pick up one row based on the latest 'Stop time' but I'm not able to figure out how to add the above filters and if-else conditions in there.

    SELECT  [Case Number],
ROW_NUMBER ( )  
    OVER ( PARTITION BY [Case Number] order by [Stop time] desc )  idx
      ,[Stage]
      ,[Time left]
      ,[SLA definition]
      ,[Elapsed time]
      ,[Elapsed percentage]
      ,[Start time]
      ,[Stop time]
      ,[Has breached]
      ,[Breach time]
      ,[Updated]
      ,[Updated by]
      ,[Created]
      ,[Created by]


  FROM ( select * from [SLA_Data] where Stage != 'Cancelled' )v1

Answer 1

It's a bit hard to tell from your question but something like this is my interpretation (I'm not able to access SQL, nor did you provide enough test data so cannot test it)

   select * from
    ( 
    SELECT  [Task],...,...,
    ROW_NUMBER ( )      
    OVER ( PARTITION BY Task order by 
        case 
        when Stage in('In Progress' ,'Paused') then 1
        when Stage='Completed' then 2 end,
        [Stop time] desc )  idx
    ) 
    where idx=1