good day,
trying to find the best way to drop one row based on a criteria. I need to find the duplicate Dates by PatID, but when duplicates on the same date, keep A and C, but drop B. Keep B if no duplicates on the same date. Thank you.
So for
PATID = 1
1; A; 2020-08-05
1; C; 2020-08-04
PATID = 2
2; A; 2020-08-21
2; C; 2020-08-26
PATID = 3
3; C; 2020-10-01
3; A; 2020-10-05
3; B; 2020-10-06
IF OBJECT_ID('TempDB..#pat','U') IS NOT NULL
DROP TABLE #pat
create table #pat
(PatID varchar(10)
,ElementID varchar(50)
,ElementIDDate date)
Insert into #Pat
values
(1, 'A', '2020-08-05')
,(1, 'B', '2020-08-04')
,(1, 'C', '2020-08-04')
,(2, 'B', '2020-08-26')
,(2, 'A', '2020-08-21')
,(2, 'C', '2020-08-26')
,(3, 'C', '2020-10-01')
,(3, 'B', '2020-10-05')
,(3, 'A', '2020-10-05')
,(3, 'B', '2020-10-06')
Select
p.patid
,p.elementid
,p.ElementIDDate
from #pat p
December 23, 2021 at 5:55 pm
Maybe a DELETE statement like this. [Edit: got rid of window function approach and went with COUNT(*) ]
with rn_cte(PatID, ElementIDDate) as (
select PatID, ElementIDDate
from #pat p
group by PatID, ElementIDDate
having count(*)>1)
delete p
from #pat p
join rn_cte r on p.PatID=r.PatID
and p.ElementIDDate=r.ElementIDDate
where p.ElementID='B';
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
delete p
from #pat p
where p.ElementID='B'
and exists(select 1
from #pat pp
where pp.PatID=p.PatID
and pp.ElementIDDate=p.ElementIDDate
and pp.ElementID<>'B');
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 24, 2022 at 6:14 am
This was removed by the editor as SPAM
February 24, 2022 at 6:16 am
This was removed by the editor as SPAM
April 8, 2022 at 6:02 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply