Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Finding previous value
 
From:   Vinay Bagare

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016