My goal is to calculate the final end dollar amt, the math logic is
the new end dollar amt = minimum(beginning_balance_amt,net_dollar_balance), beginning balance amount = previous end balance amount
For example, I have two snowflake tables as follow:
Table A
| Category | Beginning Dollar AMT | End Dollar AMT | Month |
|---|---|---|---|
| A | 1000 | 1000 | 2021-01-31 |
| B | 500 | 500 | 2023-02-28 |
Table B
| Category | Net Dollar balance | Month |
|---|---|---|
| A | 800 | 2021-02-28 |
| A | 2000 | 2021-03-31 |
| A | 300 | 2021-04-30 |
| B | 0 | 2023-03-31 |
| B | 200 | 2023-04-30 |
My expected output table is:
Table C
| Category | Beginning Dollar AMT | End Dollar AMT | Month |
|---|---|---|---|
| A | 1000 | 800 | 2021-02-28 |
| A | 800 | 800 | 2021-03-31 |
| A | 800 | 300 | 2021-04-30 |
| B | 500 | 0 | 2023-03-31 |
| B | 0 | 0 | 2023-04-30 |
The challenge for me is I have to code it in SAS with snowflake tables, and I am not sure how to do this in SAS or snowflake.
to carry state acrross the rows, you will need to use a language that allows carrying of state, my goto is a javascript UDTF, of which there are many on this site, for this exact problem of carrying balance forward.
stackoverflow.com/a/75378334/43992 or stackoverflow.com/a/59832844/43992