I'm trying to come up with an OLAP function solution to a units deduction problem for various groups.
We have 3 vendors that receive product orders each week. Some weeks a vendor can receive multiple orders. There is a desired number of total units we want for that week, but sometimes the vendor sends too many. We would call the excess amount overage. We want to then go back to each week and deduct the overage units from each order, going from the most recent paid to the earliest for the week, until the overage amount for the week for that vendor is 0.
See below for an example of what I would like to do in Teradata:
Vendor Week Order Nbr Order Da Amount Produced for Order Total Produced in Week Amount Desired for Week Overage for Week Units Needed to Deduct Units Deducted from Order New Order Units Amount
ABC Silicon WEEK 1 1_1A000 5/6/2016 1000 1500 1000 500 500 500 500
ABC Silicon WEEK 1 1_1B000 5/3/2016 500 1500 1000 500 0 0 500
ABC Silicon WEEK 2 1_2A000 5/11/2016 200 200 200 0 0 0 200
GH Tech WEEK 2 2_1A000 5/13/2016 300 1000 800 200 200 200 100
GH Tech WEEK 2 2_2B000 5/9/2016 700 1000 800 200 0 0 700
Almira WEEK 1 3_1A000 5/7/2016 600 1100 500 600 400 400 200
Almira WEEK 1 3_1B000 5/5/2016 100 1100 500 600 200 100 0
Almira WEEK 1 3_1C000 5/2/2016 400 1100 500 600 100 100 300
Almira WEEK 2 3_2A000 5/13/2016 600 1400 700 700 700 600 0
Almira WEEK 2 3_2B000 5/12/2016 800 1400 700 700 100 100 700
Here is the SQL I am using, and I am not getting the above output. It does a good job deducting overage units on the first order for the week, but then takes out the entire overage amount from the second order for the week, instead of just taking the remaining overage units after accounting for the previous order.
SELECT VENDOR_NAME, WEEK, ORDER_NBR, ORDER_DATE, AMOUNT_PROD, WKLY_TOTAL_PROD, AMOUNT_NEED, OVERAGE_AMT
, SUM(AMOUNT_PROD) OVER(PARTITION BY VENDOR_NAME, WEEK_NBR ORDER BY ORDER_DATE ROWS UNBOUNDED PRECEDING) AS RUNNING_TOTAL
, COALESCE(SUM(AMOUNT_PROD) OVER (PARTITION BY VENDOR_NAME, WEEK_NBR ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LAST_TOTAL
, CASE WHEN LAST_TOTAL=0 AND OVERAGE_AMT >=AMOUNT_PROD THEN AMOUNT_PROD
WHEN LAST_TOTAL=0 AND OVERAGE_AMT <AMOUNT_PROD THEN OVERAGE_AMT
WHEN LAST_TOTAL >0 AND REMAINING_TO_DEDUCT=0 THEN 0
WHEN LAST_TOTAL >0 AND REMAINING_TO_DEDUCT>=AMOUNT_PROD THEN AMOUNT_PROD
WHEN LAST_TOTAL>0 AND REMAINING_TO_DEDUCT < AMOUNT_PROD THEN REMAINING_TO_DEDUCT
END AS DEDUCT_AMT
, COALESCE(SUM(OVERAGE_AMT-DEDUCT_AMT) OVER(PARTITION BY VENDOR_NAME, WEEK_NBR ORDER BY ORDER_DATE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS REMAINING_TO_DEDUCT
, AMOUNT_PROD-DEDUCT_AMT AS NEW_ORDER_AMT
ORDER BY VENDOR_NAME, WEEK_NBR, ORDER_DATE DESC;
Also - I have included what DEDUCT_AMT should ideally be in the code (logic I want it to use), realistically the code won't run like that because you cannot 'nest' OLAP functions - "REMAINING TO DEDUCT".