Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 02 Jan 2008 @ 20:14:06 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Finding previous value
 
From:   ulrich arndt

Krithiga,

as already stated - both rows in your example have the same item_num and item_advc_eff_dt.

So what is the rule for picking 23456 instead of 12345?

Or should 23456 have an earlier date?

If so - what do you want in case no previous row exists? nothing or the actual row?

In both cases would be the solution - as already stated - to use something like two instances of OLAP max or min function

like (not tested pseudo code...)

-actual value in case no previous exists

     select item_num,
             coalesce(
                 max(ADVC_NUM) over (partiton by item_num rows between 1
     preceding and 1 preceding)
                      ,ADVC_NUM
                      ) as ADVC_NUM
     from table_x
     qualify ITEM_ADVC_EFF_DT = max(ITEM_ADVC_EFF_DT) over (partiton by
     item_num)

- only rows if previous exists

     select item_num,
             max(ADVC_NUM) over (partiton by item_num rows between 1 preceding
     and 1 preceding) as ADVC_NUM
     from table_x
     qualify ITEM_ADVC_EFF_DT = max(ITEM_ADVC_EFF_DT) over (partiton by
     item_num) and max(ADVC_NUM) over (partiton by item_num rows between 1
     preceding and 1 preceding) is not null

Ulrich



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016