CODEWITHSUNDEEP
sqlsql-serverrow-number
jackstraw22
jackstraw22

Reputation: 641

SQL window function to remove multiple values with different criteria

I have a data set where I'm trying to remove records with the following conditions:

If a practid has multiple records with the same date and at least one record has a reason of "L&B" then I want all the practid's for that date to be removed.

DECLARE t table(practid int, statusdate date, reason varchar(100)

INSERT INTO t VALUES (1, '2018-03-01', 'L&B'),
                     (1, '2018-03-01', 'NULL'),
                     (1, '2018-04-01, 'R&D'),
                     (2, '2018-05-01, 'R&D'),
                     (2, '2018-05-01, 'R&D'),
                     (2, '2018-03-15', NULL),
                     (2, '2018-03-15', 'R&D),
                     (3, '2018-07-01, 'L&B)

With this data set I would want the following result:

PractId  StatusDate   Reason
1         2018-04-01   R&D
2         2018-05-01   R&D
2         2018-05-01   R&D
2         2018-03-15    NULL
2         2018-03-15   R&D

I tried solving this with a window function but am getting stuck:

SELECT *, ROW_NUMBER() OVER
    (PARTITION BY practid, statusdate, CASE WHEN reason = 'L&B' THEN 0 ELSE 1 END) AS rn
FROM table

From my query I can't figure out how to keep Practid = 2 since I would want to keep all the records.

Upvotes: 1

Views: 256

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522151

To continue along your current approach, we can use COUNT as an analytic function. We can count the occurrences of the L&B reason over each practid/statusdate window, and then retain only groups where this reason never occurs.

SELECT practid, statusdate, reason
FROM
(
    SELECT *,
        COUNT(CASE WHEN reason = 'L&B' THEN 1 END) OVER
            (PARTITION BY practid, statusdate) cnt
    FROM yourTable
) t
WHERE cnt = 0;

Demo

Upvotes: 2

D-Shih
D-Shih

Reputation: 46239

You can try to use not exists with a subquery.

 Select *
 from t t1
 where not exists (
     select 1 
     from t tt
     where tt.reason = 'L&B' and t1.statusdate = tt.statusdate
 )

sqlfiddle

Upvotes: 2

Related Questions