'Remove unwanted rows from dataset in SQL

Below is a billing list that shows a credit on an account for TestProcedure 880.0200 First the test is added (NEW Action) then the charge is removed (DEL ACTION). I would like to remove both entries in the ledger so it only shows what is actually being charged.

My goals is to say: IF "DEL" ACTION is present, remove the "DEL" ACTION entry ROW along with the corresponding Test Procedure code "NEW" ACTION Entry ROW.

Thoughts?

VisitID RequisitionID   DateTime    SeqID   Type    TestProcedure   Action  BillCount   Charge  
G201    45  2022-02-14 00:00:00.000 1   L   109.0200    NEW 1   83.33   
G201    45  2022-02-14 00:00:00.000 2   L   300.0500    NEW 1   0.00    
G201    45  2022-02-14 00:00:00.000 3   L   880.0200    NEW 1   19.70   
G201    45  2022-02-14 00:00:00.000 4   L   880.0200    DEL -1  -19.70  
G201    45  2022-02-14 00:00:00.000 5   L   310.5200    ADD 1   66.67

OUTPUT would look like this:

VisitID RequisitionID   DateTime    SeqID   Type    TestProcedure   Action  BillCount   Charge
G201    45  2022-02-14 00:00:00.000 1   L   109.0200    NEW 1   83.33
G201    45  2022-02-14 00:00:00.000 2   L   300.0500    NEW 1   0.00
G201    45  2022-02-14 00:00:00.000 5   L   310.5200    ADD 1   66.67
sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions