Archives of the TeradataForum

Message Posted: Thu, 03 Jan 2008 @ 18:35:37 GMT

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

Krithiga,

Check if this SQL helps you (you may want to format it for better readibility).

Sample records considered

     item_num advc_num effective_date
     278051 519284 03/22/2007
     278051 519291 03/23/2007
     278051 519284 03/25/2007
     278051 519293 03/25/2007
     278051 550949 11/04/2007
     278051 550953 11/04/2007
     278051 550962 11/04/2007



     SEL
     Item_Num
     ,Advc_Num
     ,Effective_Date

     ,CASE WHEN SUM(Item_Num) OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num
     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - Item_Num = 0 THEN Item_Num
     ELSE SUM(Item_Num) OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num
     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - Item_Num END AS Item_Num_1

     ,CASE WHEN SUM(Advc_Num) OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num
     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - Advc_Num = 0 THEN Advc_Num
     ELSE SUM(Advc_Num) OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num
     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - Advc_Num END AS Advc_Num_1

     ,ROW_NUMBER() OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num) AS RowSeq

     ,COUNT(*) OVER(ORDER BY Effective_Date ASC, Item_Num, Advc_Num) AS RowCnt

     FROM TEST

     WHERE Effective_Date < '2007/11/04'
     QUALIFY RowSeq = RowCnt
     ORDER BY 3 ASC, 1, 2;

Output

     item_num  advc_num  effective_date  Item_Num_1  Advc_Num_1  RowSeq  RowCnt
       278051    519293      03/25/2007      278051      519284       4       4

Hope this helps!

Thanks,

Vinay Bagare

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