Date Difference between consecutive rows

by 
SELECT  T1.ID, 
        T1.AccountNumber, 
        T1.Date, 
        MIN(T2.Date) AS Date2, 
        DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM    YourTable T1
        LEFT JOIN YourTable T2
            ON T1.AccountNumber = T2.Accountnumber
            AND T2.Date > T1.Date
GROUP BY T1.ID, T1.AccountNumber, T1.Date;

or

SELECT  ID,
        AccountNumber,
        Date,
        NextDate,
        DATEDIFF("D", Date, NextDate)
FROM    (   SELECT  ID, 
                    AccountNumber,
                    Date,
                    (   SELECT  MIN(Date) 
                        FROM    YourTable T2
                        WHERE   T2.Accountnumber = T1.AccountNumber
                        AND     T2.Date > T1.Date
                    ) AS NextDate
            FROM    YourTable T1
        ) AS T

More Related Contents:

  1. SQL Access ” Count “? [closed]
  2. How do I use parameters in VBA in the different contexts in Microsoft Access?
  3. Upserting in MS-access
  4. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  5. How to simulate UNPIVOT in Access?
  6. Case expressions in Access
  7. SQL Query – Using Order By in UNION
  8. How do I count unique items in field in Access query?
  9. Pivoting data in MS Access
  10. coalesce alternative in Access SQL
  11. Using Excel VBA to export data to MS Access table
  12. Top n records per group sql in access
  13. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  14. Why does a LIKE query in Access not return any records?
  15. How to SUM two fields within an SQL query
  16. [] brackets in sql statements
  17. How to use system username directly in MS Access query?
  18. can we list all tables in msaccess database using sql?
  19. Use Access SQL to do a grouped ranking
  20. Access – compare two tables and update or insert data in first table
  21. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  22. SQL Updating from an inner join
  23. How to delete in MS Access when using JOIN’s?
  24. SQL: Using NULL values vs. default values
  25. How do I create a table alias in MySQL
  26. How to save the result of a SQL query into a variable in VBA?
  27. Select query with date condition
  28. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  29. Microsoft Access – Case Query
  30. CONCAT equivalent in MS Access

Leave a Comment