Max Nbr of Months W/ No Activity in Past 12 Months

How about this one? (Because you posted minimal info, I reserve the right to create my own DDL as I interpret).

    IF OBJECT_ID ('tempdb..#') IS NOT NULL
        DROP TABLE #sales
    GO 
    CREATE TABLE #sales
        (Dte datetime
        ,amt int
        ,id int
        ,rownum int identity PRIMARY KEY
        )   

    INSERT INTO #sales
    SELECT '9/1/17',1200, 987
    INSERT INTO #sales
    SELECT '10/1/17',1100, 987
    INSERT INTO #sales
    SELECT '11/1/17',1000, 987
    INSERT INTO #sales
    SELECT '5/1/18',1900, 987
    INSERT INTO #sales
    SELECT '6/1/18',120, 987

    INSERT INTO #sales
    SELECT '4/1/17',100, 657
    INSERT INTO #sales
    SELECT '5/1/17',190, 657
    INSERT INTO #sales
    SELECT '7/1/17',12, 657

    GO

    SELECT MAX(DATEDIFF(dd,a.dte,b.dte)), a.id
    FROM #sales a
    INNER JOIN #sales b
        ON a.rownum = b.rownum-1
        AND a.id =b.id
    GROUP BY a.id

Browse More Popular Posts

Leave a Comment