Incremental Date Update for Groups

0
votes

I have data in this format:

ID| DATE
--------
1 | 02/23/2014
1 | 05/15/2011
1 | 01/05/2017
2 | 12/21/2013
3 | 10/10/2016
3 | 09/05/2015

For Each ID Group I want to update the dates starting from yesterday onward(decreasing order). Considering todays date is 09/28/2017, Output should be like:

ID | DATE
--------
1 | 09/27/2017
1 | 09/26/2017
1 | 09/25/2017
2 | 09/27/2017
3 | 09/27/2016
3 | 09/26/2017

My approach was (which is not working)

UPDATE TABLE
SET DATE = CURRENT DATE - p.a days
FROM (
    SELECT DATE
        ,ID
        ,ROW_NUMBER() OVER (
            PARTITION BY ID ORDER BY ID ASC
            ) a
    FROM TABLE
    ) p
WHERE DATE = p.DATE
    AND ID = p.ID
WITH ur;

Kindly suggest any method to achieve the same.

Edit:

Database : DB2

Error :

ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "from" was found following "rent date - p.a days". Expected tokens may include: "".

Date Column Data type: Date

2
sqldb2db2-luw
Which dbms are you using? (That query attempt is product specific.) - jarlh
Column date's data type? - jarlh
which error you get? - MtwStark
Ordering by the column under partition will have no effect on the results. I'd not be surprised if the optimizer drops the clause. Did you mean 'DATE'? If those are the only two columns you have, though, the ordering is irrelevant (since the data is overwritten). - Clockwork-Muse
have you solved? have you found any answer useful? - MtwStark

2 Answers

0
votes

You can use this.

DECLARE @Today DATE = GETDATE()

SELECT ID,  
    DATEADD(DAY
         , -1 * (ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE] DESC)), @Today) [DATE] 
FROM MyTable

For Update 

DECLARE @Today DATE = GETDATE()

;WITH T AS (
    SELECT 
        ID, 
        [DATE],  
        DATEADD(DAY,-1 * (ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE] DESC)), @Today) [NEW_DATE] 
    FROM MyTable
)
UPDATE M 
    SET [DATE] = T.[NEW_DATE]
FROM 
    MyTable M INNER JOIN T ON  M.ID = T.ID AND M.[DATE] = T.[DATE]
0
votes

this should do the trick

for db2:

update t 
    set DATE = NewDate
from TABLE t
join (
    select T.DATE, T.ID, (CURRENT DATE - ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.ID ASC)) NewDate
    FROM TABLE T
) p  on t.DATE = p.DATE and t.ID = p.ID

for sql-server:

update t 
    set DATE = DATEADD(DAY, -p.a, GETDATE())
from TABLE t
join (
    select T.DATE, T.ID, ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.ID ASC) a
    FROM TABLE T
) p  on t.DATE = p.DATE and t.ID = p.ID