Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-26-2016
01:16 PM

07-26-2016
01:16 PM

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".