datastage Cyclic variable equivalent in bigquery sql

Question

enter image description here

enter image description here

Can someone please give the exact solution as per bigquery equivalent.

SR_NO   MAX_FAC_RMB_PRV_INFO_SEQ_NO FAC_RMB_PRV_INFO_SEQ_NO SvKeyFlag   SvMaxFacRmbSeqNoCal(expected o/p)   (Output i am getting)
1   2   0   Y   1   1
2   4   0   Y   2   2
3   7   0   N   8   8
4   0   18  Y   18  18
5   5   0   Y   19  19
6   6   0   Y   20  20
7   4   0   N   5   5
8   12  0   Y   6   6
9   10  0   Y   7   7
10  14  0   Y   8   7
11  15  0   N   16  16
12  0   0   Y   0   0
13  18  0   N   19  19
14  19  0   N   20  20
15  20  0   Y   21  21

I have prepared the below as solution but this is not giving the proper solution in bigquery .

Solution:

WITH CTE_SvMaxFacRmbSeqNoCal AS (
SELECT SR_NO,
CASE 
  WHEN MAX_FAC_RMB_PRV_INFO_SEQ_NO <> 0 AND FAC_RMB_PRV_INFO_SEQ_NO = 0 THEN 
    CASE 
      WHEN SvKeyFlag = 'N' THEN MAX_FAC_RMB_PRV_INFO_SEQ_NO + 1
      ELSE (CASE WHEN i.SR_NO = 1 THEN 0 ELSE NULL END ) +1  
    END
  ELSE 
    FAC_RMB_PRV_INFO_SEQ_NO
END AS v_SvMaxFacRmbSeqNoCal 
FROM DB_SRCP1.CYCLIC_8_NOV i
ORDER BY SR_NO 
)
,
CTE2 AS (SELECT i.* , CTE.v_SvMaxFacRmbSeqNoCal,  LAST_VALUE(CTE.v_SvMaxFacRmbSeqNoCal IGNORE NULLS) OVER (ORDER BY i.SR_NO  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) LST_VAL,
COALESCE(CTE.v_SvMaxFacRmbSeqNoCal, LAST_VALUE(CTE.v_SvMaxFacRmbSeqNoCal IGNORE NULLS) OVER (ORDER BY i.SR_NO  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1) AS SvMaxFacRmbSeqNoCal
FROM DB_SRCP1.CYCLIC_8_NOV i 
LEFT JOIN CTE_SvMaxFacRmbSeqNoCal CTE ON (i.SR_NO = CTE.SR_NO)
ORDER BY i.SR_NO 
)

SELECT * , CASE WHEN SvMaxFacRmbSeqNoCal = LAG(SvMaxFacRmbSeqNoCal) OVER(ORDER BY SR_NO ) THEN SvMaxFacRmbSeqNoCal+ 1 ELSE SvMaxFacRmbSeqNoCal end SVMAX, 1 ro,
COALESCE(CTE2.v_SvMaxFacRmbSeqNoCal, LAST_VALUE(CTE2.SvMaxFacRmbSeqNoCal IGNORE NULLS) OVER (ORDER BY CTE2.SR_NO  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1) AS SvMaxFacRmbSeqNoCal
FROM  CTE2 
ORDER BY SR_NO 


SR_NO   MAX_FAC_RMB_PRV_INFO_SEQ_NO FAC_RMB_PRV_INFO_SEQ_NO SvKeyFlag   SvMaxFacRmbSeqNoCal (expected o/p)  Output i am getting
1   2   0   Y   1   1
2   4   0   Y   2   2
3   7   0   N   8   8
4   0   18  Y   18  18
5   5   0   Y   19  19
6   6   0   Y   20  20
7   4   0   N   5   5
8   12  0   Y   6   6
9   10  0   Y   7   7
10  14  0   Y   8   7
11  15  0   N   16  16
12  0   0   Y   0   0
13  18  0   N   19  19
14  19  0   N   20  20
15  20  0   Y   21  21

I have tried the above query .. and i want the solution as per expected o/p column