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

by

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

More Related Contents:

  1. LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
  2. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  3. Find a value anywhere in a database
  4. Function to Calculate Median in SQL Server
  5. How to check if a column exists in a SQL Server table
  6. Sql Server string to date conversion
  7. Select columns from result set of stored procedure
  8. Pass Dictionary to Stored Procedure T-SQL
  9. SQL time difference between two dates result in hh:mm:ss
  10. What is the best way to truncate a date in SQL Server?
  11. Dynamic SQL (passing table name as parameter)
  12. Understanding SQL Server LOCKS on SELECT queries
  13. Get dates from a week number in T-SQL
  14. Date conversion and culture: Difference between DATE and DATETIME
  15. How to check if a database exists in SQL Server?
  16. How to remove accents and all chars a..z in sql-server?
  17. tsql returning a table from a function or store procedure
  18. How can I do something like: USE @databaseName
  19. How to split string and insert values into table in SQL Server
  20. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  21. How can I get the sum of multiple datetime values?
  22. Use of Begin / End Blocks and the Go keyword in SQL Server?
  23. How do you determine what SQL Tables have an identity column programmatically
  24. Ampersand (&) operator in a SQL Server WHERE Clause
  25. Avg of float inconsistency
  26. how to write number to word function in sql server [closed]
  27. Is there an “IF EXISTS” test for a linked server?
  28. ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException
  29. Does the `nodes()` method keep the document order?
  30. SQL Server stored procedure return code oddity

Leave a Comment