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

Tags (2)
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_dtFROM tabQUALIFY    ROW_NUMBER()   OVER (PARTITION BY prod_id, vld_to_dt          ORDER BY price) = 1`
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 dtQUALIFY -- 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

Highlighted
Junior Contributor

Have a look at