Archives of the TeradataForum

Message Posted: Fri, 04 Jan 2008 @ 17:54:46 GMT

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

<-- Anonymously Posted: Fri, 04 Jan 2008 11:16 -->

Krithiga,

I think your request is pretty vague but, if it's safe to assume that the CURRENT advc_num for an item_num, effective_dt combination is the GREATEST advc_num associated with that item_num, effective_dt combination then this is how I would do it. If not, then you can ignore this.

     sel a.item_num , a.curr_effective_dt , a.curr_advc_num
     , b.prev_effective_dt , b.prev_advc_num
     from

     (sel item_num as item_num, effective_dt as curr_effective_dt, max(advc_num)
     as curr_advc_num
     from TABLE
     where effective_dt = '11/04/2007' /* date entered */
     group by 1,2) a

     , (sel item_num as item_num, effective_dt as prev_effective_dt, max(advc_num)
       as prev_advc_num
     from TABLE
     where (item_num, effective_dt) in
     (select item_num, max(effective_dt)
     from TABLE
     where effective_dt < '11/04/2007' /* date entered */
     group by 1)
     group by 1,2) b

     where a.item_num = b.item_num
     order by 1

Data used:

     item_num   advc_num   effective_dt
      278051     550962     11/04/2007
      278051     550953     11/04/2007
      278051     550949     11/04/2007
      278051     519293     03/25/2007
      278051     519291     03/23/2007
      278051     519284     03/25/2007
      278051     519284     03/22/2007
      278052     650962     11/04/2007
      278052     650953     11/04/2007
      278052     650949     11/04/2007
      278052     619291     04/04/2007
      278052     619284     04/04/2007

Results:

     item_num   curr_effective_dt   curr_advc_num   prev_effective_dt   prev_advc_num
      278051       11/04/2007          550962          03/25/2007          519293
      278052       11/04/2007          650962          04/04/2007          619291

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