Analytical Function for deriving dates

Database

Analytical Function for deriving dates

Hi Everyone,

                    I am new to Teradata forum. Could anyone in forum please help out in deriving an analytical function.

Trying to create a new date column using windowing function which display date of child (SEQ_ORDER (5)) as is and move dates of its first parent (SEQ_ORDER (4)) to all the parent level i.e 3,2,1

Table:

       CREATE MULTISET TABLE TEMP

     (

      CHILD VARCHAR(50) ,

      CHILD_TYPE VARCHAR(50) ,

      PARN VARCHAR(50) ,

      SEQ_ORDER INTEGER,

      CRT_DATE DATE )

PRIMARY INDEX ( CHILD ,CHILD_TYPE );

Samle Inserts:

INSERT INTO TEMP VALUES ('017292','TEST_HIER','RMTYPE','1','2015-04-17');      

INSERT INTO TEMP VALUES ('017292','TEST_HIER','BCH_BUS','2','2015-04-17');

INSERT INTO TEMP VALUES ('017292','TEST_HIER','BCHTOT','3','2015-04-17');

INSERT INTO TEMP VALUES ('017292','TEST_HIER','SBUS','4','2015-04-17');

INSERT INTO TEMP VALUES ('017292','TEST_HIER','017292','5','2016-04-17');

INSERT INTO TEMP VALUES ('017398','TEST_HIER','RMTYPE','1','1900-01-01');      

INSERT INTO TEMP VALUES ('017398','TEST_HIER','TOT','2','1900-01-01');

INSERT INTO TEMP VALUES ('017398','TEST_HIER','UNMGD','3','2014-01-28');

INSERT INTO TEMP VALUES ('017398','TEST_HIER','DL','4','2014-02-28');

INSERT INTO TEMP VALUES ('017398','TEST_HIER','017398','5','2014-03-07');

CHILD   CHILD_TYPE    PARN          SEQ_ORDER  CRT_DATE           EXPECTED_OUTPUT

017292    TEST_HIER    RMTYPE          1          2015-04-17                  2015-04-17

017292    TEST_HIER    BCH_BUS       2          2015-04-17          2015-04-17

017292    TEST_HIER    BCHTOT         3          2015-04-17          2015-04-17

017292    TEST_HIER    SBUS             4          2015-04-17          2015-04-17

017292    TEST_HIER    017292         5          2016-04-17                 2016-04-17

017398    TEST_HIER    RMTYPE         1          2015-04-17               2015-04-17

017398    TEST_HIER    TOT               2          2015-05-17          2015-03-17

017398    TEST_HIER    UNMGD         3          2015-02-17          2015-03-17

017398    TEST_HIER    DL                4          2015-03-17          2015-03-17

017398    TEST_HIER    017292        5              2014-04-17                  2016-04-17

Trying with the Query, but not working as expected: 

SELECT CHILD,

CHILD_TYPE,

PARN,

SEQ_ORDER,

CRT_DATE

 ,  MAX(CRT_DATE) OVER (PARTITION BY CHILD  ,CHILD_TYPE ORDER BY SEQ_ORDER DESC

      RESET WHEN CRT_DATE /* current row Leaf Dept */ >

      MAX(CRT_DATE) OVER (PARTITION BY  CHILD  ,CHILD_TYPE ORDER BY SEQ_ORDER ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) /* prev row  Non Leaf */     

      ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW ) AS EXPECTED_OUTPUT

from  TEMP;

Kindly any technical suggestions please.


Thanks

1 REPLY

Re: Analytical Function for deriving dates

Hi Everyone,

                    I had tried the below query and its working for scenario when parent level from 3,2,1 is having dates less than 4

SELECT CHILD,

CHILD_TYPE,

PARN,

SEQ_ORDER,

CRT_DATE

 ,  MAX(CRT_DATE) OVER (PARTITION BY CHILD  ,CHILD_TYPE ORDER BY SEQ_ORDER DESC

      RESET WHEN ROW_NUMBER() OVER (PARTITION BY CHILD  ,CHILD_TYPE ORDER BY SEQ_ORDER DESC) = 2

      ROWS BETWEEN UNBOUNDED PRECEDING  AND  UNBOUNDED FOLLOWING ) AS EXPECTED_OUTPUT

from  TEMP;

Thanks