All Forums Database
crazyrama5 4 posts Joined 02/12
09 Aug 2016
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

 

Johannes Vink 28 posts Joined 08/14
09 Aug 2016

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.

crazyrama5 4 posts Joined 02/12
09 Aug 2016

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
 
 

You must sign in to leave a comment.