Trying to get DateDiff Based on One Field and Update Another Field

Question

I am trying to update DaysInPeriod with the DateDiff function, based on the change in EFFECTIVESTARTDATE field.

Here is my DLL:

DROP TABLE Reporting_Table 
CREATE TABLE Reporting_Table (
    Credit_Line_NO       Varchar(10),
    CURRENCY             VARCHAR(3),
    AMOUNT               INT,
    StartDate            DATE,
    EFFECTIVESTARTDATE   DATE,
    EXPIRY_DATE          Date,
    FREQUENCY            INT,
    CO_CODE              VARCHAR(10),
    AsOfDate             Date,
    SOURCEID_REVISED     VARCHAR(255),
    PID                  VARCHAR(5),
    DaysInPeriod         INT
)

INSERT INTO Reporting_Table(CREDIT_LINE_NO,CURRENCY,AMOUNT,STARTDATE,EFFECTIVESTARTDATE,EXPIRY_DATE,FREQUENCY,CO_CODE,ASOFDATE,SourceID_Revised,PID,DaysInPeriod)
VALUES
('1026321','USD','16875','9/30/2017','9/30/2017','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','12/31/2017','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','3/31/2018','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','6/30/2018','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','9/30/2018','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','12/31/2018','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','3/31/2019','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026321','USD','16875','9/30/2017','6/30/2019','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026329','USD','16875','9/30/2017','9/30/2017','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026329','USD','16875','9/30/2017','12/31/2017','9/30/2019','8','US0010001','7/31/2017','','',''),
('1026329','USD','16875','9/30/2017','3/31/2018','9/30/2019','8','US0010001','7/31/2017','','','')

Select * From Reporting_Table

Select *
From Reporting_Table

I have this SQL:

with cte as 
(
select *, rn = row_number() over (partition by Credit_Line_NO,ASOFDATE order by ASOFDATE)
from Reporting_Table
)
Select *
From cte

Basically, when rn=1, DaysInPeriod = 90, and then it should increment by DateDiff(days,rn-1,rn) for every next rn. It should reset based on the change in Credit_Line_NO & ASOFDATE, so I am using:

partition by Credit_Line_NO,ASOFDATE

Here is a sample of what I want to achieve.

I am using SQL Server 2008, so I can't use the Lead/Lag functions. I put together the SQL below, but it doens't execute.

SELECT  T1.CREDIT_LINE_NO, 
        T1.CURRENCY,
        T1.AMOUNT,
        T1.STARTDATE,
        T1.EFFECTIVESTARTDATE,
        T1.EXPIRY_DATE,
        T1.FREQUENCY,
        T1.CO_CODE,
        T1.AsOfDate
        MIN(T2.EFFECTIVESTARTDATE) AS Date2, 
        DATEDIFF("D", T1.EFFECTIVESTARTDATE, MIN(T2.EFFECTIVESTARTDATE)) AS DaysDiff 
FROM    Reporting_Table T1
        LEFT JOIN Reporting_Table T2
        ON T1.CREDIT_LINE_NO = T2.CREDIT_LINE_NO
        AND T2.EFFECTIVESTARTDATE > T1.EFFECTIVESTARTDATE 
GROUP BY T1.CREDIT_LINE_NO, 
        T1.CURRENCY,
        T1.AMOUNT,
        T1.STARTDATE,
        T1.EFFECTIVESTARTDATE,
        T1.EXPIRY_DATE,
        T1.FREQUENCY,
        T1.CO_CODE,
        T1.AsOfDate

Finally, I want to run an UPDATE query, or SELECT * INTO NEW_TABLE query.

Answer 1

Your query fails because line 9 T1.AsOfDate is missing a comma. Joining on AND T2.EFFECTIVESTARTDATE > T1.EFFECTIVESTARTDATE creates a 1 to many join which is not necessary. We can imitate a LAG function by applying row_number in a CTE then joining on T1.rn = T2.rn +1.

Edit: I updated your ROW_NUMBER to order by EFFECTIVESTARTDATE since ASOFDATE is a partition column and will always be the same within a window.

Here is the SQL fiddle for this solution.

You can SELECT INTO this result set into a new table or UPDATE an existing table.

WITH cte AS (
    SELECT
        Credit_Line_NO,
        CURRENCY,
        AMOUNT,
        StartDate,
        EFFECTIVESTARTDATE,
        EXPIRY_DATE,
        FREQUENCY,
        CO_CODE,
        AsOfDate,
        SOURCEID_REVISED,
        PID,
        DaysInPeriod,
        ROW_NUMBER() OVER (PARTITION BY Credit_Line_NO, ASOFDATE ORDER BY EFFECTIVESTARTDATE) AS rn
    FROM Reporting_Table
    )

SELECT
    T1.Credit_Line_NO,
    T1.CURRENCY,
    T1.AMOUNT,
    T1.StartDate,
    T1.EFFECTIVESTARTDATE,
    T1.EXPIRY_DATE,
    T1.FREQUENCY,
    T1.CO_CODE,
    T1.AsOfDate,
    T1.SOURCEID_REVISED,
    T1.PID,
    CASE
        WHEN T1.rn = 1 THEN 90
        ELSE DATEDIFF("D", t2.effectivestartdate, t1.effectivestartdate)
        END AS DaysInPreiod,
    T1.rn
FROM cte AS t1
LEFT JOIN cte AS t2 ON
    t1.credit_line_no = t2.credit_line_no
    AND t1.rn = t2.rn + 1