I need to reset the running total when two conditions are met.
See update below for clarification.
Using SQL SERVER
Current Table: item_Number|cartons|average_cartons|running_total| 123456 |.1 |.181818 |.1 | 123456 |.1 |.181818 |.4 | 123456 |.1 |.181818 |.5 | 123456 |.2 |.181818 |.7 | 123456 |.1 |.181818 |.8 | 123456 |.1 |.181818 |.9 | 123456 |.2 |.181818 |1.1 | 123456 |.3 |.181818 |1.4 | 123456 |.7 |.181818 |2.0 | 200000 |3.00 |16.25 |3.0 | 200000 |4.00 |16.25 |7.0 | 200000 |8.00 |16.25 |15.0 | 200000 |1.25 |16.25 |16.25 |
Desired Table: item_number|cartons|average_cartons|running_total|resetting_total 123456 |0.10 |.181818 |0.1 |0.1 123456 |0.10 |.181818 |0.4 |0.4 123456 |0.10 |.181818 |0.5 |0.5 123456 |0.20 |.181818 |0.7 |0.7 123456 |0.10 |.181818 |0.8 |0.8 123456 |0.10 |.181818 |0.9 |0.9 123456 |0.20 |.181818 |1.1 |0.1 123456 |0.30 |.181818 |1.4 |0.4 123456 |0.70 |.181818 |2.0 |0.0 200000 |3.00 |16.25 |3.0 |3.0 200000 |4.00 |16.25 |7.0 |7.0 200000 |8.00 |16.25 |15.0 |15.0 200000 |1.25 |16.25 |16.25 |0
UPDATE 1:
The Data:
The data is partitioned by item number and ordered by the week of the year. Therefore,
- Item_Number = the item number.
- Cartons = the number of cartons sold that week per item number.
- Average_cartons = the average number of cartons sold per week.
- running_total = the running sum of cartons across the weeks by item number.
- resetting_total = the running sum of cartons of an item_number since > average_cartons.
- Groups: I’m not sure if it is necessary. Other answers have included a group variable to sum all of the values within the group. I have also seen an out of stock boolean to accomplish this. See:[OOS answer].1
The resetting_total column resets when both of the following conditions are met.
Conditions:
The running_total is > 1. This accounts for averages that are less than the value 1.
The running total is >= average_cartons.
My Goal:
- To roughly estimate when I need to order a set of cartons.
Assume:
That at least 1 carton is ordered to account for weekly averages < 1.
That each order will be equal to average_cartons rounded to the next integer (ceiling(average_cartons)).
Advertisement
Answer
There are two parts to the calculation. First get the maximum of 1 and average cartons as a divisor. In other words, make sure it’s at least 1. Then compute the remainder using modular arithmetic. Group number is similar being just the integer part of the quotient whereas reset is the fractional part.
select
floor(running_total /
case when average_cartons > 1 then average_cartons else 1.0 end) as grp, /* if you need it */
running_total %
case when average_cartons > 1 then average_cartons else 1.0 end as reset_total
from <your query>