Sql: filter if condition applies

See more:

SQL

Oracle

database

data

Hello guys, quick question I have this set of data that repeats when ever a case change status, let's say we have assigned, unassigned, work in progress, closed, etc. I need to remove the instances of when a case is changed to closed, not downloading any data when the query is executed.

here is an example of a case:

type       caseid   businesssource      status
dummytype    921ed59f   dummy            CLOSED
dummytype    921ed59f   dummy            CLOSED
dummytype    921ed59f       dummy            UNASSIGNED
dummytype    921ed59f   dummy            UNASSIGNED

Hence in this case I need this data to be completed deleted as it's already a closed case.

What I have tried:

Here is an example of what I've been trying to do but with no success.

    select

     status
    , caseid
    , domain
    , marketplaceid
    , concat ('Q', datepart(quarter, creationdate)) as created_quarter
    , case when marketplaceid = 1 then 'US'
           when marketplaceid = 3124564 then 'GO'
           when marketplaceid = 5646897 then 'PUS'
           when marketplaceid = 3213165 then 'Te'
           when marketplaceid = 32132131 then 'FED'
           else 'LOV'
      end as marketplace
    , assignee
    , businesssource
    , queue
    , creationdate
    , closuredate
    , identifier 
    -- datefiff (day, closuredate, creationdate ) as SLA
    , ROW_NUMBER() OVER (PARTITION BY caseid, queue, identifier order by status asc) as rnk

    from dummytable

    where status in ('WORK_IN_PROGRESS','ASSIGNED','UNASSIGNED','PENDING_VENDORSELLER_COMMUNICATION')
    and trunc(creationdate) between '2019-01-01' and sysdate
    and domain not in ('Demo')