Sql-server – Select complete next row based on the current row

sql serversql-server-2012t-sql

Hi i have a complicated condition. I have a table lets say 'Test' 

 ID    Partner   Type       Amount
143854  CSTC    Purchase    -0.81
144029  CSTC    Purchase    -0.69
144030  CSTC    Purchase    -1.33
144031  CSTC    Purchase    -0.47
144032  CSTC    Purchase    -1.8
149527  CSTC    div         1574.48
149528  CSTC    Purchase    -1574.48
149531  CSTC    div         867.53
149532  CSTC    Purchase    -867.53
149539  CSTC    div         123
149540  CSTC    div         76.2
149541  CSTC    Purchase    -76.2
149550  CSTC    div          8.77
149551  CSTC    Purchase    -8.77
149554  CSTC    div         700.45
149555  CSTC    Purchase    -700.45

I want to remove each occurrence of rows having Type = 'div' and the next row should have Type = 'Purchase'

i.e if type = 'div' and next row type = 'Purchase' delete both else i want to perform some update action on the row having type = 'div'.

I have tried Lead i can get next rows Type col value but it is not helping.
Other thing i tried was 

;WITH TestWithRowNumber AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber
    FROM Test where Partner = 'CSTC' and   (Type = 'div' or Type = 'Purchase') 
)  

      Select FirstRow.* From TestWithRownumber FirstRow
  left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and     firstrow.type = 'div' where FirstRow.ID >= (select TOP 1 FirstRow.ID From TestWithRownumber FirstRow
   left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and firstrow.type = 'div' Where FirstRow.[Transaction] = 'div')

Issue with this query is in the output i am getting two consecutive div i.e an extra div whose next row is not with Type = 'Purchase'.

Best Answer

Trying to guess your requirements here, You want to "remove" rows that are part of div-Purchase pairs.

If by remove you mean don't select them, i.e. filter them out in a where clause, then something like this might work:

with TestCTE as
(
    select ID, [Partner], [Type], Amount, 
        LEAD([Type], 1) OVER (ORDER BY ID) NextType,
        LAG([Type], 1) OVER (ORDER BY ID) PreviousType
    from test
)
select ID, [Partner], [Type], Amount
from TestCTE
where NOT ( [Type] = 'div' AND [NextType] = 'Purchase' )
  and NOT ( [Type] = 'Purchase' AND [PreviousType] = 'div' )

But if by remove, you mean delete, then that's simply:

with TestCTE as
(
    select ID, [Partner], [Type], Amount, 
        LEAD([Type], 1) OVER (ORDER BY ID) NextType,
        LAG([Type], 1) OVER (ORDER BY ID) PreviousType
    from test
)
DELETE
from TestCTE
where ( [Type] = 'div' AND [NextType] = 'Purchase' )
  or ( [Type] = 'Purchase' AND [PreviousType] = 'div' )

That's two examples that both use a select and a delete using a CTE and similar where clauses. If you want to update, you can use the same strategy.

Related Question