CODEWITHSUNDEEP
sqlsql-server
Goal7
Goal7

Reputation: 19

updating date for a corresponding row value

I am trying create the newdate column in the below dataset for a given start date(4/20 in the case below) for the respective invoice number with requestdate less that the starting date(4/20). i tried setting up a counter with count of Invoice number but it didnt work.

Can someone help me here?

invoice#    RequestDate Newdate
123          4/18/2017  4/20/2017
123          4/16/2017  4/21/2017
123          4/17/2017  4/22/2017
126          4/15/2017  4/20/2017
126          4/16/2017  4/21/2017
126          4/17/2017  4/22/2017
126          4/18/2017  4/23/2017
126          4/19/2017  4/24/2017
131          4/13/2017  4/20/2017
131          4/14/2017  4/21/2017
131          4/15/2017  4/22/2017
131          4/16/2017  4/23/2017
131          4/17/2017  4/24/2017
131          4/18/2017  4/25/2017
131          4/19/2017  4/26/2017

Upvotes: 0

Views: 51

Answers (2)

benjamin moskovits
benjamin moskovits

Reputation: 5458

select invoice#,requestdate,
 row_number() over (partition by invoice# order by invoice#) + getdate() -1  as newdate
from t

Upvotes: 1

Guillaume CR
Guillaume CR

Reputation: 3016

First, let's determine the invoices you want to modify. We do this in the subquery PastInvoices. Then, you can use ROW_NUMBER() and PARTITION BY to create incrementing dates.

SELECT AllInvoices.invoice,
    RequestDate,
    CASE
        WHEN PastInvoices.invoice IS NOT NULL
        THEN DATEADD(day, (ROW_NUMBER() over (partition by AllInvoices.invoice order by AllInvoices.invoice)) - 1, GETDATE())
        ELSE RequestDate
    END as NewDate
FROM RequestDate as AllInvoices
LEFT OUTER JOIN 
    (select distinct invoice from RequestDate where RequestDate < GETDATE()) as PastInvoices
on AllInvoices.invoice = PastInvoices.invoice

Upvotes: 1

Related Questions