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.

What error message do you get from the query that doesn't execute? — Tab Alleman, Feb 22 '18 at 18:30
Your query failed because line 9, "T1.AsOfDate" does not have a comma. — Andrew O'Brien, Feb 22 '18 at 18:37
It makes no sense to partition by a column and also order by it. — shawnt00, Feb 22 '18 at 20:21

Andrew O'Brien · Accepted Answer · 2018-02-22T19:24:34.213

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

Yes, Yes! I think this will almost work, Andrew. The only thing is, and I just realized this, to get the Datediff right, I need to Order by Credit_Line_NO, AsOfDate, EFFECTIVESTARTDATE. If I add that in, I get an error about 'The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.'. I tried 'Credit_Line_NO, AsOfDate ORDER BY ASOFDATE) AS rn' and everything is rn=1. Ugh!! How can I fix this??!! — ASH, Feb 22 '18 at 19:34

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

1 Answers1