Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SUM(UNPAID_MONTHLY) OVER(PARTITION BY SAMPLE_ACCT ORDER BY MONTH_NO DESC) TOTAL_UNPAID_AMT
- select t.*,
- sum(unpaid_monthly) over (partition by sample_acct, grp order by month_no desc)
- from (select t.*,
- sum(case when unpaid_monthly = 0 then 1 else 0 end) over (partition by sample_acct order by month_no) as grp
- from t
- ) t;
- WITH t (unpaid_monthly, sample_acct, month_no) AS
- (SELECT 1335.67, 22900005, 1 FROM dual UNION ALL
- SELECT 1289.36, 22900005, 2 FROM dual UNION ALL
- SELECT 1241.95, 22900005, 3 FROM dual UNION ALL
- SELECT 1211.32, 22900005, 4 FROM dual UNION ALL
- SELECT 1179.33, 22900005, 5 FROM dual UNION ALL
- SELECT 0, 22900005, 6 FROM dual UNION ALL
- SELECT 5509.8, 22900005, 7 FROM dual UNION ALL
- SELECT 3388.59, 22900005, 8 FROM dual UNION ALL
- SELECT 1398.41, 22900005, 9 FROM dual UNION ALL
- SELECT 0, 22900005, 10 FROM dual UNION ALL
- SELECT 1717.97, 22900005, 11 FROM dual UNION ALL
- SELECT 0, 22900005, 12 FROM dual UNION ALL
- SELECT 5016.4, 22900005, 13 FROM dual)
- SELECT unpaid_monthly, sample_acct, month_no,
- sum_unpaid + unpaid_monthly AS TOTAL_UNPAID_AMT
- FROM t
- MATCH_RECOGNIZE (
- PARTITION BY sample_acct
- ORDER BY month_no
- MEASURES
- FINAL SUM(unpaid_monthly) - SUM(unpaid_monthly) AS sum_unpaid
- ALL ROWS PER MATCH
- PATTERN (a+ b?)
- DEFINE
- a AS unpaid_monthly > 0);
- UNPAID_MONTHLY SAMPLE_ACCT MONTH_NO TOTAL_UNPAID_AMT
- =============================================================
- 1335.67 22900005 1 6257.63
- 1289.36 22900005 2 4921.96
- 1241.95 22900005 3 3632.6
- 1211.32 22900005 4 2390.65
- 1179.33 22900005 5 1179.33
- 0 22900005 6 0
- 5509.8 22900005 7 10296.8
- 3388.59 22900005 8 4787
- 1398.41 22900005 9 1398.41
- 0 22900005 10 0
- 1717.97 22900005 11 1717.97
- 0 22900005 12 0
- 5016.4 22900005 13 5016.4
Add Comment
Please, Sign In to add comment