Looping in teradata using window functions

Database

Looping in teradata using window functions

The highlighted rows in the input needs to be merged to one row and populate the (min)date and (max)date of the block as the from and to date in the new record.

Whenever , there is a continuation of Flag =1

Can the below result be achieved using Teradata window functions :

 

Input   
IDFrom_DateTo_DateFlag
abc1233/31/20174/7/20171
abc1234/7/20174/10/20170
abc1234/10/20174/12/20171
abc1234/12/20174/12/20170
abc1234/12/20175/1/20171
abc1235/1/20175/2/20171
abc1235/2/20175/8/20171
abc1235/8/20177/3/20170
abc1237/3/20177/3/20171
abc1237/3/20179/1/20170
abc1239/1/20179/1/20171
abc1239/1/201710/31/20170
    
Output   
IDFrom_DateTo_DateFlag
abc1233/31/20174/7/20171
abc1234/7/20174/10/20170
abc1234/10/20174/12/20171
abc1234/12/20174/12/20170
abc1234/12/20175/8/20171
abc1235/8/20177/3/20170
abc1237/3/20177/3/20171
abc1237/3/20179/1/20170
abc1239/1/20179/1/20171
abc1239/1/201710/31/20170

 

Thanks for the help !!

3 REPLIES
Teradata Employee

Re: Looping in teradata using window functions

Seems more like a use case for TD_NORMALIZE_MEET function (see the SQL Reference: SQL Functions, Operators, Expressions, and Predicates manual).

If there are never multiple rows in with Flag=0 in sequence, or if you wanted to merge those also, it should be fairly straightforward.

Otherwise apply TD_NORMALIZE_MEET to Flag=1 rows and UNION ALL with the Flag=0 rows.

Re: Looping in teradata using window functions

Thanks Fred .

 

There can be multiple rows for flag=0 but it's not required to be merge. it will be treated separate records.

I will try the TD_NORMALIZE_MEET , its a new one for me . 

Re: Looping in teradata using window functions

Hi Fred

 

Can you help me create the query . I tried by myseld but didnt get the correct result :

 

SyntaxEditor Code Snippet

WITH cte AS (
   SELECT system_id,
      PERIOD(DEC_FROM_DATE-1, DEC_TO_DATE) AS pd, -- create a period      OPEN_MATCH_F
         FROM ADW_DM_SIRA_BACKUP.DECISIONS_TEMP    
  
)SELECT system_id,
   Begin(pd)+1 as startdt, End(pd) as enddt, -- split the period    OPEN_MATCH_F 
FROM    
        TABLE (TD_NORMALIZE_meet                (NEW VARIANT_TYPE(cte.system_id, cte.OPEN_MATCH_F)
                ,cte.pd)
        RETURNS (system_id   VARCHAR(256)
              ,OPEN_MATCH_F byteint          ,pd PERIOD(DATE)
          ,Cnt INTEGER)

        HASH BY system_id, OPEN_MATCH_F
        LOCAL ORDER BY system_id,   OPEN_MATCH_F, pd 
        ) dt
        order by system_id,startdt,enddt