All Forums Teradata Applications
AnuJoseph 2 posts Joined 03/16
21 Mar 2016
Problem to reset date column in a scenario

Hi,
I have an issue with a particular data scenario. Below is the initial data set:

ID_COL1	DATE
123	20150601
123	20150602
123	20150603
123	20150605
123	20150607
456	20150401
456	20150420
456	20150421

The reuqirement is to reset the dates falling within 3 days to the first date. Once we find a date outside the 3 day bucket, it has to reset. The expected result set is as below:

ID_COL1	DATE	DERVED DATE
123	20150601	20150601
123	20150602	20150601
123	20150603	20150601
123	20150605	20150605
123	20150607	20150605
456	20150401	20150401
456	20150420	20150420
456	20150421	20150420

I have tried using 'RESET WHEN' clause. But it doesnt seem to work for all cases.  Finally had to go for procedures to get this done which runs for hours as data volume is high.
Is there any way this can be done in a VIEW or SQL?
Thanks in advance!

dnoeth 4628 posts Joined 11/04
23 Mar 2016

This is a kind of logic which can't be done without recursion/loop.
Did you use a CURSOR or loop? Probably a cursor based on "runs for hours" :)
 
If the number of rows per ID_COL1 is not too high the easiest solution is based on recursion:

-- need to create a table first to be able to implement the next-row cursor-logic:
CREATE VOLATILE TABLE vt AS 
 (
   SELECT ID_COL1, dt,
      ROW_NUMBER()
      OVER (PARTITION BY ID_COL1
            ORDER BY dt) AS rn
   FROM tab
 ) WITH DATA
PRIMARY INDEX (ID_COL1)
ON COMMIT PRESERVE ROWS

WITH RECURSIVE cte (ID_COL1, dt, newdt, rn) AS
 (
   SELECT ID_COL1, dt AS dt, dt AS newdt, rn
   FROM vt
   WHERE rn = 1 -- start eith the first row
  
   UNION ALL
    
   SELECT vt.ID_COL1, vt.dt, 
     -- check if it's more than three days from the first date
       CASE WHEN vt.dt >= cte.newdt + 3 THEN vt.dt ELSE cte.newdt END,
       vt.rn
   FROM cte JOIN vt 
     ON cte.ID_COL1 = vt.ID_COL1
    AND cte.rn + 1 = vt.rn
 )
SELECT * FROM cte
ORDER BY 1,rn

 

Dieter

AnuJoseph 2 posts Joined 03/16
24 Mar 2016

This works like a charm! :) Thanks a bunch.
And yes the 'run for hours' was beacuse I used a cursor.

You must sign in to leave a comment.