I have a SQL Server query that returns a dataset showing a person's ID, the entity that person works for, the locations belonging to the entity, the coordinator assigned to the case and the person's status.
ID EntityName LocationName AssignedTo StatusName
17 F&S St. Lucie A Hardon Active
17 F&S St. Lucie A Hardon Withdrawn
18 F&S NH A Hardon Withdrawn
20 H&H NCH B Reedy Active
I need to eliminate records where the only status for the combination of EntityName, LocationName and AssignedTo is Withdrawn. So in the dataset above I would want to remove ID = 18 with LocationName = NH.
I've tried using a Window Function, which may be on the right path but I'm not sure how to proceed:
Select id, entityname, locationname, assignedto, statusname
Into #test
From Table A
Select *,
row_number()over(partition by entityname, locationname, assignedto
order by case when statusname = 'Withdrawn' then 1
else 2 end) as rn
from #test
This gives me a result like this:
ID EntityName LocationName AssignedTo StatusName RN
17 F&S St. Lucie A Hardon Withdrawn 1
17 F&S St. Lucie A Hardon Active 2
18 F&S NH A Hardon Withdrawn 1
20 H&H NCH B Reedy Active 1
But now I'm stuck on how to continue, or if I'm even going about this the wrong way.
try using EXISTS
Select *
FROM #test t1
WHERE EXISTS (SELECT *
FROM #test t2
WHERE t1.EntityName = t2.EntityName
and t1.LocationName = t2.LocationName
and t1.AssignedTo = t2.AssignedTo
and t2.StatusName <> 'Withdrawn')