CODEWITHSUNDEEP
sqlsql-server
Albert Makamure
Albert Makamure

Reputation: 25

Updating Status when there is a duplicate

enter image description here

How can I update my status to 1 if the condition is same Reference, but 1 reference should be Debit and 1 credit. If they are 3 records with same Reference but 2 with same debit amount and 1 credit 2 records should be updated to 1 that is 1 with credit and 1 with Debit using SQL

Upvotes: 0

Views: 65

Answers (1)

Squirrel
Squirrel

Reputation: 24763

Looks like you want to identify lines where debit is same as credit

The row_number() inside the cte is to generate a sequence number for matching Debit with Credit.

The matching is handle at sum() over () expression partition by Reference, coalesce(Debit, Credit), r

with trans as
(
    select  Reference, Debit, Credit,
            r = row_number() over (partition by Reference, Debit, Credit
                    order by Debit, Credit)     
    from    tbl 
)
select  *,
        case when sum ( isnull(Debit, 0) - isnull(Credit, 0) ) 
                 over ( partition by Reference, coalesce(Debit, Credit), r ) = 0
             then 1
             else 0 
             end
from    trans;

dbfiddle demo

Upvotes: 1

Related Questions