Archives of the TeradataForum

Message Posted: Thu, 04 Aug 2011 @ 20:16:56 GMT

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

Debojit,

Try something like this for the query:

     sel lvl2.*,
     max(act_oh_invnt) over (partition by ftyirlsku, set_id)
     this_is_what_you_want
     from
     (-- lvl2 carry row number from is_first_of_set to all rows in set as
     set_id
     sel lvl1.*,
     max(is_first_of_set) over (partition by ftyirlsku order by wk_no rows
     between unbounded preceding and current row) set_id
     from
     (-- lvl1 row preceding 1 or more nulls is identified by row number
     sel a.*,
     case when a.act_oh_invnt is not null -- need to do this in case last row
     in partition has null act_oh_invnt
               and
               max(act_oh_invnt) over (partition by ftyirlsku order by wk_no
                                       rows between 1 following and 1 following) is null
          then row_number() over (partition by ftyirlsku order by wk_no)
     is_first_of_set
     from temp_DB.adas_tmp1_div7_02 a) lvl1) lvl2

Lvl1 checks each non-null act_oh_invnt to see if the next one is null. If so, the row is identified by its row number within the partition.

Lvl2 assigns that row number to all following null rows until a new row number is hit, calling that set_id

The outermost query gets the max act_oh_invnt over each ftyirlsku/set_id partition, which will be the only non-null act_oh_invnt in the partition.

Might be some typos or a step only partially implement, but ought to be enough to get you started.

Dave

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