• raghuldrag - Monday, December 18, 2017 9:12 PM

    drew.allen - Monday, December 18, 2017 1:19 PM

    First, when working with dates, store them as dates.  Part of the reason that you're having problems is that you aren't storing your dates as dates.

    The main issue that people run into when trying to solve this problem, is that they try to preserve information that is irrelevant for the problem at hand.  Specifically, if you're looking at differences in years, anything more granular than a year is irrelevant.

    You also talk about inputs, but then your sample data is a table.  I did a self join to get two dates to work with.

    /* Step 1: Adjust the whole fiscal year to match the calendar year. DATEADD(MONTH, -3, <datefield>)
        Step 2: Find the difference in years between the two adjusted values.
    */
    ;
    WITH Test_Corrected AS
    (
        SELECT *
        FROM #Test
        CROSS APPLY ( VALUES( CAST('01 ' + RTRIM(M2) + ' ' + CASE WHEN M2 IN ('Jan', 'Feb', 'Mar') THEN RIGHT(M1, 4) ELSE LEFT(M1, 4) END AS DATE)) ) fy(fy_month_start)
    )
    SELECT FromDate.fy_month_start AS FromDate, ToDate.fy_month_start AS ToDate, DATEDIFF(YEAR, DATEADD(MONTH, -3, FromDate.fy_month_start), DATEADD(MONTH, -3, ToDate.fy_month_start)), *
    FROM Test_Corrected FromDate
    INNER JOIN Test_Corrected ToDate
        ON FromDate.fy_month_start < ToDate.fy_month_start
    ORDER BY FromDate.fy_month_start, ToDate.fy_month_start

    Drew

    My database is Ms sql 2000 so  this with clause wont support

    Yes, but SQL 2000 does support derived tables and a CTE and derived table are equivalent in this case.  SQL 2000 also doesn't support the CROSS APPLY, but that was only used to prevent having to repeat the formulas.  You should be able to figure out how to translate this information into something that will work in SQL 2000.

    Also, SQL 2000 is no longer supported.  Why are you still on SQL 2000?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA