SQL Server 2016: how to delete records that are not duplicated but have duplicate values?
I am new to here and relatively new to SQL. I have a table like this:
[Pk], [Case_No], [Status], [Open_Date], [Close_Date], [Case_Age], [Report_Date]
Every week, all cases, open or closed, are dropped into a table with a new report date. What I want to do is delete the deleted entries that have already been closed. I want to keep the first instance where the case was reported as closed, but I don't need duplicates (if it's not already open). Hopefully this happens as ... it has been a long day. I already have a solution in the future, but I need to clear the historical records in order to report correctly.
If I could figure out how to add a flag or something ... I tried Dense_Rank()
, but I don't want the zero closing dates to be ranked ...
I've been looking for ideas for days and now I'm reaching out to experts for suggestions. Any help is greatly appreciated! Thanks in advance!
source to share
Using a common table expression (cte) and : row_number()
with cte as (
select *
, rn = row_number() over (
partition by Case_No, [Status]
order by Report_Date asc
)
from t
where [Status] = 'Closed'
)
select *
from cte
where rn > 1
To remove them, just change select *
to delete
.
source to share