Archives of the TeradataForum

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

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

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>>