All Forums Database
lr_shp 1 post Joined 07/16
26 Jul 2016
OLAP Function for Recurring Deduction

Hi All, 
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
FROM VENDOR_WEEKLY_ORDERS
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". 

You must sign in to leave a comment.