SQL - Flag rows till 0 value of each group-CodePudding

I am calculating a running balance and want to flag all rows till 0 value to have 'MATCHED' flag else 'NOT-MATCHED' flag with respect to account ID.

Here is what I have tried but didn't got proper result:

SEL a.*, 
CASE WHEN RUNNING_BALANCE OVER (PARTITION BY ACCT_SROGT_ID ROWS UNBOUNDED PRECEDING ) = 0 THEN 'M' ELSE 'NM' END R 
FROM NON_MATCHING_RUNNING_BALANCE  a

CodePudding user response：

We can use a sub-query to find the last acct_rank which is 0 and then use case to test each row.

Select
  a.*,
  Case when a.acct_rank > z.last_zero
    Then 'unmatched' else 'matched' 
    End as is_matched
From accounts a
Join ( select 
         account_id as id,
         MAX(acct_rank) last_zero
       From accounts
       Where running_balance = 0
       Group by account_id) z
On a.account_id = z.id;

CodePudding user response：

Check this one

WITH SUB AS  (
select NON_MATCHING_RUNNING_BALANCE.*,row_number()over(partition by [Account ID] order by [Account ID]) RN from NON_MATCHING_RUNNING_BALANCE
)
SELECT SUB.*,CASE WHEN SUB.RN<=SUB2.RN AND SUB.[Account ID]=SUB2.[Account ID] THEN 'MATCHED' ELSE 'Not-Matched' END AS 'MATCH' FROM SUB LEFT JOIN (
SELECT * FROM SUB WHERE value=0 ) SUB2 ON SUB.[Account ID]=SUB2.[Account ID]