0

I'm trying to set up a cumulative inventory function, where for each material I'm trying to calculate future inventory. Because of imbalance with orders, I might have cases where the rolling sum might go below zero. I'd like to avoid these, cap the sum to zero (avoid going to negative), and pick back up at the next positive transaction

This is for SAP HANA, with supports regular CTE's but not regular CTE's as far as I understand. I was able to get a standard SUM OVER going. 

+----------+-------------+----------+----------+---------------+
| Material |    Date     | Quantity | SUM(NOW) | SUM(Expected) |
+----------+-------------+----------+----------+---------------+
| A        | 07/01/2019  |      100 |      100 |           100 |
| A        | 07/02/2019  |      -20 |       80 |            80 |
| A        | 07/03/2019  |      -70 |       10 |            10 |
| A        | 07/04/2019  |      -20 |      -10 |             0 |
| A        | 07/05/2019  |       40 |       30 |            40 |
| A        | 07/06/2019  |      -20 |       10 |            20 |
+----------+-------------+----------+----------+---------------+

A sample table of current and expected results is shown above

gilliali
  • 13
  • 4
  • similar examples without the full answer for reference can be found here: 1 - https://stackoverflow.com/questions/51726241/cumulative-sum-without-going-below-zero 2 - https://stackoverflow.com/questions/41987502/sql-capped-continuous-sum – gilliali Jul 12 '19 at 03:17
  • This requires a recursive CTE. – Gordon Linoff Jul 12 '19 at 12:17

0 Answers0