Get Minimum Price from Overlapping valid dates

General
Enthusiast

Get Minimum Price from Overlapping valid dates

Hi , Need your help !!! New to Teradata 

prod_id    price     vld_fr_dt        vld_to_dt

801462  100.00   2/9/2003     12/31/9999

801462  178.00  4/18/2004    12/31/9999

801462  205.00  2/9/2003      12/31/9999

801462  50.00    5/18/2005    12/31/9999

801462  250.00  1/5/2003      2/8/2003

Need output like : 

prod_id    price     vld_fr_dt        

801462  50.00    5/18/2005    

801462  250.00  1/5/2003      


i.e basically the minimum price if the date overlaps 

7 REPLIES
Junior Contributor

Re: Get Minimum Price from Overlapping valid dates

For your example this should be a simple:

SELECT
prod_id, price, vld_fr_dt
FROM tab
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY prod_id, vld_to_dt
ORDER BY price) = 1
Highlighted
Enthusiast

Re: Get Minimum Price from Overlapping valid dates

Hi Dieter , Thanks for your answer 

It's failing for below case 

801462    50.00             2/9/2006    3/10/2010

801462    205.00           2/9/2003    12/31/9999

801462    178.00           4/18/2004  12/31/9999

here the output should be 

801462    50.00             2/9/2006    3/10/2010

because the date overlaps and it's having the minimum price 

Junior Contributor

Re: Get Minimum Price from Overlapping valid dates

Now it's much more complicated :-)

Similar questions have been asked multiple times, you need nested OLAP functions to get that result:

SELECT *
FROM
(
SELECT prod_id, vld_fr_dt, price,
SUM(flag) -- assign a number to this group of rows
OVER (PARTITION BY prod_id
ORDER BY vld_fr_dt, flag DESC
ROWS UNBOUNDED PRECEDING) AS grp
FROM
(
SELECT prod_id, vld_fr_dt, vld_to_dt, price,
CASE
WHEN -- find the gap, i.e. no overlapping for this start date
MAX(vld_to_dt)
OVER (PARTITION BY prod_id
ORDER BY vld_fr_dt--,vld_to_dt
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
> vld_fr_dt
THEN 0
ELSE 1
END AS flag
FROM tab
) AS dt
) AS dt
QUALIFY -- find the row with the lowest price
ROW_NUMBER()
OVER (PARTITION BY prod_id, grp
ORDER BY price) = 1
Enthusiast

Re: Get Minimum Price from Overlapping valid dates

Hi Dieter , 

Works Perfect ! Thanks a lot !!!

Enthusiast

Re: Get Minimum Price from Overlapping valid dates

Could you please suggest how can we master in such OLAP usages !! thanks in advance 

Enthusiast

Re: Get Minimum Price from Overlapping valid dates

801462    50.00            2/9/2006    3/10/2010

801462    205.00           2/9/2003    12/31/9999

801462    178.00           4/18/2004   12/31/9999

if analyse the periods  

First price came in on    :  801462    205.00           2/9/2003    12/31/9999

Second price came in on :    801462    178.00           4/18/2004   12/31/9999

i.e.                         801462    205.00           2/9/2003    4/17/2004  - would exists 

and                          801462    205.00           4/18/2004   12/31/9999  - also this will exist 

and again 3rd row came in :  801462    50.00            2/9/2006    3/10/2010

here                         801462    178.00           2/9/2003    2/8/2006  would exists 

and                          801462    178.00           2/9/2006    12/31/9999 

and                          801462    178.00           3/10/2010   12/31/9999 would exist 

Output should be :

801462    205.00           2/9/2003    4/17/2004  -- not overlapping 

Plus Minimum of 

801462    178.00           4/18/2004   12/31/9999

801462    205.00           4/18/2004   12/31/9999

Which is : 801462    178.00           4/18/2004   12/31/9999

Plus minimum of 

801462    178.00           2/9/2006    12/31/9999

801462    50.00            2/9/2006    3/10/2010

Which is 801462    50.00            2/9/2006    3/10/2010

Plus 

801462    178.00           3/10/2010   12/31/9999 - not overlapping 

Final O/p:

801462    205.00           2/9/2003    4/17/2004

801462    178.00           4/18/2004   12/31/9999  --2nd row 

801462    50.00            2/9/2006    3/10/2010

801462    178.00           3/10/2010   12/31/9999 -- we can eliminate this because this period is already covered in 2nd row 

It's very confusing 

Junior Contributor

Re: Get Minimum Price from Overlapping valid dates

Have a look at

forums.teradata.com/forum/database/overlapping-time-periods-competing-records-by-date-created

Your task might be similar...