I am trying to figure out a way to update dates in one field based on dates in another field

Question

Here is what I have:

Here is what I want:

If I count rownumbers by Credit_Line_NO and AsOfDate, I can find the max rn per AsOfDate. I want to make the Next_Payment_Date equal Expiry_Date_Revised. Then, make each prior Next_Payment_Date go back the number of months in the 'FBNK_MONTHS' field. Most of the time this is 3, but it could be 1 or 12.

I'm starting off like this:

   select 
        *, rn = row_number() over (partition by Credit_Line_NO order by AsOfDate)
    from 
        TBL_FBNK_LIMIT_HIST_TRANS

I'm not sure what the next step is. Does any SQL expert here know how to do this?

I am using SQL Server 2008.

Here is my DLL:

--DROP TABLE Reporting_Table
CREATE TABLE Reporting_Table (
    Credit_Line_NO       Varchar(10),
    Next_Payment_Date    Date,
    AsOfDate             Date,
    EXPIRY_DATE_REVISED      Date,
    FBNK_MONTHS           INT
)

INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 
INSERT INTO Reporting_Table (Credit_Line_NO, Next_Payment_Date, AsOfDate, EXPIRY_DATE_REVISED, FBNK_MONTHS)
Values('9938810',' 12/31/2017',' 10/31/2017',' 10/4/2018', 3) 

Perhaps something like the script below, but I keep getting an error about 'Incorrect syntax near the keyword 'AS''

with cteMain as (
    select 
        *,  rn = row_number() over (partition by Credit_Line_NO order by AsOfDate) AS sn
    from TBL_FBNK_LIMIT_HIST_TRANS
)
Select m.Credit_Line_NO, 
        m.NEXT_PAYMENT_DATE, 
        m.AsOfDate, 
        m.EXPIRY_DATE_REVISED,
        m.rn, 
        m.FBNK_MONTHS
From cteMain as m 
LEFT OUTER JOIN cteMain AS sLead
ON sLead.sn = m.sn+1
LEFT OUTER JOIN cteMain AS sLeg
ON sLeg.sn = m.sn-1
ORDER BY m.Credit_Line_NO, m.AsOfDate

Answer 1

Here is the corrected version of your query:

with cteMain as (
    select 
        *,  row_number() over (partition by Credit_Line_NO order by AsOfDate) as sn
    from Reporting_Table
)
Select m.Credit_Line_NO, 
        sLead.NEXT_PAYMENT_DATE, 
        m.AsOfDate, 
        m.EXPIRY_DATE_REVISED,
        m.sn, 
        m.FBNK_MONTHS
From cteMain as m 
LEFT OUTER JOIN cteMain AS sLead
ON sLead.sn = m.sn+1
LEFT OUTER JOIN cteMain AS sLeg
ON sLeg.sn = m.sn-1
ORDER BY m.Credit_Line_NO, m.AsOfDate