Problem to reset date column in a scenario

Teradata Applications

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!

2 REPLIES
Senior Apprentice

Re: Problem to reset date column in a scenario

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

Re: Problem to reset date column in a scenario

This works like a charm! :) Thanks a bunch.

And yes the 'run for hours' was beacuse I used a cursor.