Need help with interdependent calculation using result from previous row

Database
Enthusiast

Need help with interdependent calculation using result from previous row

Hi all, I've run into a problem that I'm having a hard time solving in Teradata. I have a query that returns a cust_id along with sell_date and a date_span (number). From that one date and number value, I need to create two calculated dates that are interdependent. One is an adjusted_sell_date, and the other is an "end_date". The end_date will be the (adjusted_sell_date + date_span - 1), but the adjusted_sell_date needs to refer to the end_date of the previous row (partitioned over cust_id) to return the later of sell_date or end_date + 1. I can do this for one iteration using the "preceding" function, but I can't seem to figure out how to solve for when the dates get pushed back several rows in a row. 

Here's a sample of the results I'm looking for:

CUST_ID     SELL_DATE    SUPPLY_DAYS   ADJUSTED_SELL_DATE    END_DATE
122333 1/4/2016 30 1/4/2016 2/2/2016
122333 2/1/2016 30 02/03/16 3/3/2016
122333 2/29/2016 30 03/04/16 4/2/2016
122333 3/30/2016 30 04/03/16 5/2/2016
122333 4/28/2016 30 05/03/16 6/1/2016
122333 5/31/2016 30 06/02/16 7/1/2016
122333 6/23/2016 30 07/02/16 7/31/2016


Here is a simplified version of the query, where adjusted_sell_date and end_date are what need to be calculated:

select
user_id,
sell_date,
supply_days,
adjusted_sell_date, --the later of (sell_date) or (adjusted_end_date+1 from the previous row)
end_date --adjusted_sell_date+supply_days-1
from sales
9 REPLIES
Teradata Employee

Re: Need help with interdependent calculation using result from previous row

The Teradata Ordered Analytics functionality can help you here especially with its windowing feature.

You can code something similar to the following:

MAX(sell_date) OVER (PARTITION BY cust_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

This will give you the prior value of sell_date in the current row.

The between value can be explanded to any number and by USING UNBOUNDED PRECEDING, it takes all prior rows into account.

Enthusiast

Re: Need help with interdependent calculation using result from previous row

Thank you for your input, Kevin. Your suggestion of using partition by and preceding was what I had come up with and doesn't work, the problem being that it needs to look at the calculated value from the previous row, which itself was calculated from the previous row, and so on (still partitioned by cust_id). If you look at the sample above, the adjusted_sell_date keeps getting pushed back further and further on each consecutive row. 

This is easily accomplished in Excel by starting with the first row as a given, then creating a formula to calculate adjusted_sell_date based on the sell_date vs the previous row's end_date (e.g. if(b3>=e2,e2+1,b3), and end_date is simply the adjusted_sell_date + supply_days - 1. I just can't figure out how to keep pushing the dates out as needed. I'm assuming it's going to involve a loop or recursive query, but based on the examples I've found online I can't figure out how to apply them to my scenario. 

Enthusiast

Re: Need help with interdependent calculation using result from previous row

Guess this one works

  SEL 
CUST_ID
,SELL_DATE
,SUPPLY_DAYS
,ROW_NUMBER () OVER ( PARTITION BY CUST_ID ORDER BY SELL_DATE) AS MAIN_ROW_CNT
,CASE WHEN MAIN_ROW_CNT = 1 THEN CAST ( SELL_DATE AS DATE)
ELSE MIN ( CAST( SELL_DATE AS DATE) ) OVER ( PARTITION BY CUST_ID ORDER BY SELL_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )
+ MIN ( SUPPLY_DAYS) OVER ( PARTITION BY CUST_ID ORDER BY SELL_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )
+ 1
END AS A_SELL_DATE
,CAST ( A_SELL_DATE AS DATE) + SUPPLY_DAYS AS END_DATE
FROM SANDBOX.TEST1
Enthusiast

Re: Need help with interdependent calculation using result from previous row

kirthi, unfortunately that results in the same problem. The second row is ok, but the third row fails to reference the previous row's adjusted end_date, so the adjusted sell_date occurs before the previous row's end_date.

Junior Contributor

Re: Need help with interdependent calculation using result from previous row

Based on the example data you don't need to use the previous row's data, it's just the minimum start date plus a cumulative sum of supplied days:

   min(SELL_DATE)
over (partition by cust_id)
+
coalesce(
sum(supply_days)
over (partition by cust_id
order by sell_date
rows between unbounded preceding and 1 preceding), 0) as ADJUSTED_SELL_DATE,
ADJUSTED_SELL_DATE + (supply_days -1) as END_DATE
Enthusiast

Re: Need help with interdependent calculation using result from previous row

Dieter, you're right that this would work in the example given, but there are some instances where there are gaps between the end_date and the next sell_date, in which case I need to use the sell_date (hence "return the later of sell_date or end_date + 1"). This is why the adjusted_sell_date needs to compare the given sell_date to the end_date of the previous row (which itself was calculated from the adjusted_sell_date, and so on). See the example of this scenario in bold below:

1

2

3

4

5

6

7

8

CUST_ID     SELL_DATE    SUPPLY_DAYS   ADJUSTED_SELL_DATE    END_DATE

122333       1/4/2016      30           01/04/16              2/2/2016

122333       2/1/2016      30           02/03/16              3/3/2016

122333      2/29/2016      30           03/04/16              4/2/2016

122333      4/10/2016      30           04/10/16              5/9/2016

122333      4/28/2016      30           05/10/16              6/8/2016

122333      5/31/2016      30           06/09/16              7/8/2016

122333      6/23/2016      30           07/09/16             8/7/2016


Enthusiast

Re: Need help with interdependent calculation using result from previous row

Would a loop or recursive query apply to this scenario? If so, can someone please help me understand how this can be accomplished? 

Enthusiast

Re: Need help with interdependent calculation using result from previous row

Anyone? Surely this can be done?

Junior Contributor

Re: Need help with interdependent calculation using result from previous row

This seems to be similar to forums.teradata.com/forum/database/calculation-of-prescription-drug-adherence

If this matches you need to materialize a row_number in a Volatile table and then apply a recursive query.