CODEWITHSUNDEEP
sqlsql-serverrow-number
jackstraw22
jackstraw22

Reputation: 641

Window function to remove specific records from SQL Server dataset

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.

Upvotes: 0

Views: 2989

Answers (6)

Chris
Chris

Reputation: 1702

Why not identify potential deletions using an inner join and then add a simple WHERE clause to delete 'Withdrawn' entries? 

delete from #table a
inner join (
            select entityname, locationname, assignedto, count(*)
            from #table
            group by entityname, locationname, assignedto
            having count(*) = 1 ) b
            on b.entityname = a.entityname
            and b.locationname = a.locationname
            and b.assignedto = a.assignedto
where a.statusnname = 'Withdrawn'

The inner join keeps 'potential' deletions (any combination of e, l and a with just one record), and the subsequent WHERE clause removes the appropriate record(s).

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

Delete with an INNER JOIN

DELETE T
FROM Table T
INNER JOIN 
(
    SELECT EntityName,LocationName,AssignedTo
    FROM Table
    WHERE StatusName='Withdrawn'
    GROUP BY EntityName,LocationName,AssignedTo
    HAVING COUNT(*) = 1
)D ON D.EntityName=T.EntityName AND D.LocationName=T.LocationName AND D.AssignedTo=T.AssignedTo

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

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')

Upvotes: 2

Aswani Madhavan
Aswani Madhavan

Reputation: 816

 Select id, entityname, locationname, assignedto, statusname,
    r   row_number()over(partition by entityname, locationname, assignedto
                   order by CASE statusname WHEN 'Withdrawn' THEN 1 ELSE 2 END) as rn Into #test From Table A
Select * from #test LEFT OUTER JOIN
  (
       SELECT MAX(rn) MRNO,ID FROM #test GROUP BY ID
  ) T ON T.ID = #TEST.ID WHERE rn = MRNO AND statusname ='Withdrawn'

This will eliinate rows with status withdrawn alone for combination of entityname, locationname, assignedto

Upvotes: 0

skp
skp

Reputation: 324

Try the below one:

WITH CTE 
AS (
Select id, entityname, locationname, assignedto, statusname,
        row_number()over(partition by entityname, locationname, assignedto
                       order by statusname) as rn
  From Table A)
DELETE  from CTE where rn=1  and statusname = 'Withdrawn'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Think about this a slightly different way. Count the number that are not withdrawn. If this is 0, then all are withdrawn.

The appropriate window function is a conditional aggregation, rather than a ranking function:

select t.*
from (Select t.*,
              sum(case when statusname <> 'Withdrawn' then 1 else 0 end) over
                  (partition by entityname, locationname, assignedto) as num_notwithdrawn
      from #test t
     ) t
where num_notwithdrawn = 0;

Upvotes: 2

Related Questions