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
adjusted_sell_date, --the later of (sell_date) or (adjusted_end_date+1 from the 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.
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.
Guess this one works
,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 )
END AS A_SELL_DATE
,CAST ( A_SELL_DATE AS DATE) + SUPPLY_DAYS AS END_DATE
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.
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:
over (partition by cust_id)
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
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:
| || |
Would a loop or recursive query apply to this scenario? If so, can someone please help me understand how this can be accomplished?
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.