CASE WHEN for changing duplicate value

Question

I am trying to add a case when statement which would add +1day into a date column of a record, if there are duplicates per two columns.

This would be the original table:

A	B	C
Red	Table	2023-01-01
Red	Table	2023-01-01

This would be the result:

A	B	C
Red	Table	2023-01-01
Red	Table	2023-01-02

I know using DATEADD would be enough to update the date select DATEADD( 'day',1,'2023-09-13')::date; but having trouble understanding how to grab the duplicate fields and only update one of them inside a case when statement during column selection.

EDIT: If column A & B are identical, I want the second records C columns to be updated with +1 day.

CASE    
        WHEN ROW_NUMBER() OVER (PARTITION BY a,b > 1) 
            THEN DATEADD('day', 1, a)
        ELSE a
        END AS c

Something like this would update both rows for example.

Any help appreciated.

Answer 1

You were on the right track. You should be using the result from ROW_NUMBER minus one as the number of days to add to the date:

SELECT *, DATEADD(
              'day',
              ROW_NUMBER() OVER (PARTITION BY A, B ORDER BY C) - 1,
              C) AS new_date
FROM yourTable
ORDER BY 1, 2, 3;

To address your follow up question, where you only want to increase the date when some column D does not match a certain value, you could use:

DATEADD(
    'day',
    COUNT(CASE WHEN D != 123 THEN 1 END) OVER (PARTITION BY A, B ORDER BY C) - 1,
    C)