Archives of the TeradataForum

Message Posted: Fri, 04 Jan 2008 @ 20:03:51 GMT

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

Krithiga,

Try the approach exemplified below.

Kind regards

Jonas Blomqvist

     ----------------------------------------------------------------
     -- Example: Finding a previous value
     ----------------------------------------------------------------
     CREATE VOLATILE TABLE item_advc_date
     ( item_num INTEGER NOT NULL
        ,advc_num INTEGER NOT NULL
        ,effective_date DATE FORMAT 'MM/DD/YYYY' NOT NULL
     ) PRIMARY INDEX (item_num)
     UNIQUE INDEX (item_num, advc_num, effective_date)
     ON COMMIT PRESERVE ROWS
     ;
     INSERT INTO item_advc_date VALUES (278051, 519284, '03/22/2007');
     INSERT INTO item_advc_date VALUES (278051, 519291, '03/23/2007');
     INSERT INTO item_advc_date VALUES (278051, 519284, '03/25/2007');
     INSERT INTO item_advc_date VALUES (278051, 519293, '03/25/2007');
     INSERT INTO item_advc_date VALUES (278051, 550949, '11/04/2007');
     INSERT INTO item_advc_date VALUES (278051, 550953, '11/04/2007');
     INSERT INTO item_advc_date VALUES (278051, 550962, '11/04/2007');

     SELECT
        t4.item_num
        ,t4.effective_date  AS current_effective_date
        ,MAX(t4.x_current_advc_num) AS current_advc_num
        ,MAX(t4.x_current_advc_lst) AS current_advc_lst
        ,MAX(t4.x_previous_effective_date) AS previous_effective_date
        ,MAX(t4.x_previous_advc_num) AS previous_advc_num
        ,MAX(t4.x_previous_advc_lst) AS previous_advc_lst
     FROM ( /* Get/add item values from previos date*/
     SELECT
        t3.item_num
        ,t3.effective_date
        ,t3.x_advc_num  AS x_current_advc_num
        ,t3.x_advc_lst  AS x_current_advc_lst
        ,MAX(t3.effective_date)OVER(PARTITION BY t3.item_num
          ORDER BY t3.effective_date ASC
          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          ) AS x_previous_effective_date
        ,MAX(t3.x_advc_num)OVER(PARTITION BY t3.item_num
          ORDER BY t3.effective_date ASC
          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          ) AS x_previous_advc_num
        ,MAX(t3.x_advc_lst)OVER(PARTITION BY t3.item_num
          ORDER BY t3.effective_date ASC
          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          ) AS x_previous_advc_lst
     FROM ( /* Create one record per item and date */
        SELECT
          t2.item_num
          ,t2.effective_date
          ,MAX(t2.advc_num)  AS x_advc_num
          ,(  MAX(CASE t2.x_rank WHEN 1
                THEN ''||TRIM(CAST(t2.advc_num AS FORMAT 'Z(9)9'))
                ELSE '' END)
            ||MAX(CASE t2.x_rank WHEN 2
                THEN ','||TRIM(CAST(t2.advc_num AS FORMAT 'Z(9)9'))
                ELSE '' END)
            ||MAX(CASE t2.x_rank WHEN 3
                THEN ','||TRIM(CAST(t2.advc_num AS FORMAT 'Z(9)9'))
                ELSE '' END)
            ||MAX(CASE t2.x_rank WHEN 4
                THEN ','||TRIM(CAST(t2.advc_num AS FORMAT 'Z(9)9'))
                ELSE '' END)
            /*... etc to the max number of previous advc_num to display*/
            )  (VARCHAR(500)) AS x_advc_lst
        FROM ( /* Create order-number for concatenation */
          SELECT
            t1.item_num
            ,t1.effective_date
            ,t1.advc_num
            ,RANK()OVER(PARTITION BY t1.item_num, t1.effective_date
              ORDER BY t1.advc_num DESC)  AS x_rank
          FROM item_advc_date AS t1
        ) AS t2
        GROUP BY 1,2
     ) AS t3
     ) AS t4
     /*A final group-by needed to "lock" window-funcs from date criteria*/
     GROUP BY 1,2
     --where current_effective_date = '11/04/2007'
     ;

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