Merge the continuous rows with date range( sequential rows should merge)

Database

Merge the continuous rows with date range( sequential rows should merge)

Hi All,

Can somebody please help me with following scenario. I am looking for dynamic solution of SQL. Because number of rows are not static.

CUST, OLD_ST_DT, OLD_END_DT, AMT1, AMT2 columns are kind of keys, (CUST, OLD_ST_DT, OLD_END_DT, AMT1, AMT2) should not be sequential duplicate when we see the with  NEW_ST_DT, NEW_END_DT columns.

CURRENT DATA:

CUST OLD_ST_DT OLD_END_DT NEW_ST_DT NEW_END_DT AMT1 AMT2

123    6/13/2014   8/24/2014   6/13/2014    6/21/2014      0.95    0.85

123    6/13/2014   8/24/2014   6/22/2014    7/21/2014      1.90    1.80

123    6/13/2014   8/24/2014   7/22/2014    7/28/2014      1.90    1.80

123    6/13/2014   8/24/2014   7/29/2014    8/12/2014      0.95    0.85

123    6/13/2014   8/24/2014   8/13/2014    8/21/2014      0.95    0.85

123    6/13/2014   8/24/2014   8/22/2014    8/24/2014      1.90    1.80

Expected Result:

CUST OLD_ST_DT OLD_END_DT NEW_ST_DT NEW_END_DT AMT1 AMT2

123   6/13/2014   8/24/2014    6/13/2014    6/21/2014     0.95    0.85

123   6/13/2014   8/24/2014    6/22/2014    7/28/2014     1.90   1.80

123   6/13/2014   8/24/2014    7/29/2014    8/21/2014     0.95   0.85

123   6/13/2014   8/24/2014    8/22/2014    8/24/2014     1.90   1.80

2 REPLIES
Enthusiast

Re: Merge the continuous rows with date range( sequential rows should merge)

this topic did help me in the past:

https://forums.teradata.com/forum/general/need-help-merging-consecutive-and-overlapping-date-spans

i ended up using dnoets olap function (how typical). however the period functions should also solve this. the olap function is ansi standard. the period approach is teradata specific.

Re: Merge the continuous rows with date range( sequential rows should merge)

Thanks Johannes for responding so quicly. 

we got it.

SELECT 

CUSTOMER_ACCOUNT_ID           

, OLD_RECORD_START_DT           

, OLD_RECORD_END_DT             

, BTV_FEE                       

, RSN_FEE                       

, NEW_RECORD_START_DT           

, CASE WHEN NEW_RECORD_END_DATE='9999-12-31' THEN NEW_RECORD_END_DATE ELSE NEW_RECORD_END_DATE-1 END AS NEW_RECORD_END_DT

FROM (SELECT CUSTOMER_ACCOUNT_ID, OLD_RECORD_START_DT, OLD_RECORD_END_DT, BTV_FEE, RSN_FEE, NEW_RECORD_START_DT, 

  COALESCE(MAX(X) 

           OVER (PARTITION BY CUSTOMER_ACCOUNT_ID, OLD_RECORD_START_DT, OLD_RECORD_END_DT, BTV_FEE, RSN_FEE  

                 ORDER BY NEW_RECORD_START_DT

                 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), X2) AS NEW_RECORD_END_DATE

FROM

 (

   SELECT CUSTOMER_ACCOUNT_ID, OLD_RECORD_START_DT, OLD_RECORD_END_DT, BTV_FEE, RSN_FEE, NEW_RECORD_START_DT, 

     MAX(NEW_RECORD_END_DT) 

     OVER (PARTITION BY CUSTOMER_ACCOUNT_ID, OLD_RECORD_START_DT, OLD_RECORD_END_DT, BTV_FEE, RSN_FEE 

           ORDER BY NEW_RECORD_START_DT,NEW_RECORD_END_DT

           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS X,

     MAX(NEW_RECORD_END_DT) 

     OVER (PARTITION BY CUSTOMER_ACCOUNT_ID, OLD_RECORD_START_DT, OLD_RECORD_END_DT, BTV_FEE, RSN_FEE) AS X2

   FROM (

   SEL CUSTOMER_ACCOUNT_ID           

, OLD_RECORD_START_DT           

, OLD_RECORD_END_DT             

,  BTV_FEE

, RSN_FEE                       

, NEW_RECORD_START_DT           

, CASE WHEN NEW_RECORD_END_DT='9999-12-31' THEN NEW_RECORD_END_DT ELSE NEW_RECORD_END_DT+1 END AS NEW_RECORD_END_DT

   FROM  NDW_ETL_WORK.WORK_009

)AA   

   QUALIFY NEW_RECORD_START_DT > X OR X IS NULL

 ) AS DT

 )MRG