none
How to get a DATEDIFF in the Same Field and do a Cross Apply to Expand all Records in a Table RRS feed

  • Question

  • BEFORE screen shot:

    NOTICE:

    AFTER screen shot:

    -- Here is my DDL
    -- Drop table Reporting_Table
    
    CREATE TABLE Reporting_Table (
        Credit_Line_NO    Varchar(10),
        noMonths         INT,
        EFFECTIVEDATE    Date,
        EXPIRY_DATE      Date,
        Amount           Money,
        mxDays           INT,
        mxFactor         decimal(5,4),
        Calc             Money)
    
    INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
     Values('9938810','3','3/31/2018','6/12/2020','11718.75','90','1','11718.75') 
    
    INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
     Values('2235461','1','6/30/2018','6/6/2019','12345','30','1','12345') 
    
    INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
     Values('3365434','12','6/30/2018','6/30/2019','298523.36085','365','1.01388888888889','302669.518639583') 

    I am trying to figure out how to calculate the 'FREQUENCY' as well as the fields in blue, green, and pink (pink is very easy). Basically, 'FREQ_CODE' has an 'M' character and after that I have months and days in a month. If noMonths is 3, I need to start mxDays with 90, and then find the difference in the number of days from the maturityDate field, so it's not the DATEDIFF() between two fields, but the DATEDIFF between increasing dates in the same field, grouped by Credit_Line_NO. So, the three cells in yellow start mxDays. Also, mxFactor is 1 when mxDays is 30 or 90, and it is 365/360, when mxDays is 365. Finally, the Calc is the mxDays * Amount. This is super-easy. I just can't figure out how to get the mxDays and mxFactor setup. Can someone help me out with this?

    For additional clarity, 91 days = 6/30/2018 - 3/31/2018 and 92 days = 9/30/2018 - 6/30/2018. Also, 1.0111 = 91/90 and 1.0222 = 92/90. Similarly, 0.8111 = 73/90. Finally, 1.0139 = 365/360 because noMonths = 12.


    MY BOOK

    Tuesday, February 13, 2018 3:13 AM