Using DELETE FROM with LEAD function-CodePudding

Using SQL Server 2014, I have a table that keeps track of cash register sales. If the register operator scans an item in error, it will create two records - one SALE record, and one VOID record.

TRX_ID   LINE_ID   STS     ITEM_DESC
-----------------------------------------------------
123456   1         Sale    Sunglasses Mens RS124
123456   2         Void    Sunglasses Mens RS124
123456   3         Sale    Sunglasses NXR RS977
123456   4         Void    Sunglasses NXR RS977
123456   5         Sale    Sunglasses Unisex RS355

123678   1         Sale    Sunglasses Womens RS124w
123678   2         Void    Sunglasses Womens RS124w
123678   3         Sale    Sunglasses Womens RS977w
123678   4         Sale    Sunglasses Womens RS977w
123678   5         Sale    Sunglasses Unisex RS355w

I need to write a query to delete records IF the following item within the transaction has been voided (STS='VOID'), only retain the 'net' transactions.

I'm using LEAD to have the query examine the following record, I just don't know how to combine the DELETE FROM and LEAD together. I've unsuccessfully tried using this query:

DELETE FROM SALES 
WHERE xxx IN (SELECT 
                  TRX_ID, LINE_ID, LEAD (T1.STS, 1, 0) OVER (PARTITION BY T1.TRX_ID ORDER BY T1.LINE_ID)  NEXT_STS
              FROM SALES_TRANSACTIONS T1) 
WHERE NEXT_STS = 'V'

Except outcome: the following records are deleted:

TRX_ID   LINE_ID   STS     ITEM_DESC
----------------------------------------------------
123456   1         Sale    Sunglasses Mens RS124
123456   2         Void    Sunglasses Mens RS124
123456   3         Sale    Sunglasses NXR RS977
123456   4         Void    Sunglasses NXR RS977
123678   1         Sale    Sunglasses Womens RS124w
123678   2         Void    Sunglasses Womens RS124w

CodePudding user response：

You can do this in several ways. One is to use lead in a common table expression, and then delete from that:

with extra as (
    select sts,
           lead(sts) over (partition by trx_id order by line_id) leadsts
    from   sales
)
delete 
from   extra
where  'Void' in (sts, leadsts);