All Forums General
kiranrajpv 6 posts Joined 05/11
11 Aug 2015
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 

dnoeth 4628 posts Joined 11/04
12 Aug 2015

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

 

Dieter

kiranrajpv 6 posts Joined 05/11
12 Aug 2015

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 

dnoeth 4628 posts Joined 11/04
12 Aug 2015

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

 
 

Dieter

kiranrajpv 6 posts Joined 05/11
12 Aug 2015

Hi Dieter , 
Works Perfect ! Thanks a lot !!!
 

kiranrajpv 6 posts Joined 05/11
12 Aug 2015

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

kiranrajpv 6 posts Joined 05/11
12 Aug 2015

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 

dnoeth 4628 posts Joined 11/04
12 Aug 2015

Have a look at
forums.teradata.com/forum/database/overlapping-time-periods-competing-records-by-date-created
Your task might be similar...

Dieter

You must sign in to leave a comment.