Guest User

Untitled

a guest
Jun 25th, 2018
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.10 KB | None | 0 0
  1. SUM(UNPAID_MONTHLY) OVER(PARTITION BY SAMPLE_ACCT ORDER BY MONTH_NO DESC) TOTAL_UNPAID_AMT
  2.  
  3. select t.*,
  4. sum(unpaid_monthly) over (partition by sample_acct, grp order by month_no desc)
  5. from (select t.*,
  6. sum(case when unpaid_monthly = 0 then 1 else 0 end) over (partition by sample_acct order by month_no) as grp
  7. from t
  8. ) t;
  9.  
  10. WITH t (unpaid_monthly, sample_acct, month_no) AS
  11. (SELECT 1335.67, 22900005, 1 FROM dual UNION ALL
  12. SELECT 1289.36, 22900005, 2 FROM dual UNION ALL
  13. SELECT 1241.95, 22900005, 3 FROM dual UNION ALL
  14. SELECT 1211.32, 22900005, 4 FROM dual UNION ALL
  15. SELECT 1179.33, 22900005, 5 FROM dual UNION ALL
  16. SELECT 0, 22900005, 6 FROM dual UNION ALL
  17. SELECT 5509.8, 22900005, 7 FROM dual UNION ALL
  18. SELECT 3388.59, 22900005, 8 FROM dual UNION ALL
  19. SELECT 1398.41, 22900005, 9 FROM dual UNION ALL
  20. SELECT 0, 22900005, 10 FROM dual UNION ALL
  21. SELECT 1717.97, 22900005, 11 FROM dual UNION ALL
  22. SELECT 0, 22900005, 12 FROM dual UNION ALL
  23. SELECT 5016.4, 22900005, 13 FROM dual)
  24. SELECT unpaid_monthly, sample_acct, month_no,
  25. sum_unpaid + unpaid_monthly AS TOTAL_UNPAID_AMT
  26. FROM t
  27. MATCH_RECOGNIZE (
  28. PARTITION BY sample_acct
  29. ORDER BY month_no
  30. MEASURES
  31. FINAL SUM(unpaid_monthly) - SUM(unpaid_monthly) AS sum_unpaid
  32. ALL ROWS PER MATCH
  33. PATTERN (a+ b?)
  34. DEFINE
  35. a AS unpaid_monthly > 0);
  36.  
  37.  
  38. UNPAID_MONTHLY SAMPLE_ACCT MONTH_NO TOTAL_UNPAID_AMT
  39. =============================================================
  40. 1335.67 22900005 1 6257.63
  41. 1289.36 22900005 2 4921.96
  42. 1241.95 22900005 3 3632.6
  43. 1211.32 22900005 4 2390.65
  44. 1179.33 22900005 5 1179.33
  45. 0 22900005 6 0
  46. 5509.8 22900005 7 10296.8
  47. 3388.59 22900005 8 4787
  48. 1398.41 22900005 9 1398.41
  49. 0 22900005 10 0
  50. 1717.97 22900005 11 1717.97
  51. 0 22900005 12 0
  52. 5016.4 22900005 13 5016.4
Add Comment
Please, Sign In to add comment