How to get previous rows date in SQL?

I have a table that stores the effective date column in order. In some cases as mentioned below I effective date is NULL so in that case I am supposed to pick date from previous column and add 1 day and show as effective date. It works fine when there is a date in previous row but when 2 or more consecutive rows are NULL it doesn’t work.

"Rev Release Date"      "Effectivity Date"      CHANGE_ID
2015-09-09 03:33:05                             1321
2015-02-23 08:23:38                             6456
2014-12-23 07:54:25     2014-12-23 07:52:35     5466
2014-12-16 02:49:52     2014-12-16 02:46:47     6546    
2014-08-28 03:51:57     2014-08-28 03:49:06     6769


SELECT 
CASE WHEN CHANGE_ID <> 0 AND "Effectivity Date" IS NULL THEN
LAG("Effectivity Date") OVER (PARTITION BY ID ORDER BY NVL("Rev Release Date", TO_DATE('1800-04-17', 'YYYY-MM-dd')))+1
ELSE "Effectivity Date" END "Effectivity Date_A",
"Effectivity Date"
FROM TABLE1;


Result:
"Rev Release Date"      "Effectivity Date_A"    "Effectivity Date"      CHANGE_ID
2015-09-09 03:33:05                                                     1321
2015-02-23 08:23:38     2014-12-24 07:52:35                             6456
2014-12-23 07:54:25     2014-12-23 07:52:35     2014-12-23 07:52:35     5466
2014-12-16 02:49:52     2014-12-16 02:46:47     2014-12-16 02:46:47     6546    
2014-08-28 03:51:57     2014-08-28 03:49:06     2014-08-28 03:49:06     6769



Expected Result:
"Rev Release Date"      "Effectivity Date_A"    "Effectivity Date"      CHANGE_ID
2015-09-09 03:33:05     2014-12-25 07:52:35                             1321
2015-02-23 08:23:38     2014-12-24 07:52:35                             6456
2014-12-23 07:54:25     2014-12-23 07:52:35     2014-12-23 07:52:35     5466
2014-12-16 02:49:52     2014-12-16 02:46:47     2014-12-16 02:46:47     6546    
2014-08-28 03:51:57     2014-08-28 03:49:06     2014-08-28 03:49:06     6769

Answer

Hmmm . . . I think a MAX() does what you want with ROW_NUMBER():

SELECT t1.*,
       (CASE WHEN CHANGE_ID <> 0 AND "Effectivity Date" IS NULL 
             THEN MAX("Effectivity Date") OVER (PARTITION BY ID) + ROW_NUMBER() OVER (PARTITION BY "Effectivity Date" ORDER BY "Rev Release Date")
             ELSE "Effectivity Date"
        END) "Effectivity Date_A"
FROM TABLE1 t1;

Advertisement

Answer