Date Difference between consecutive rows

SELECT  T1.ID,         T1.AccountNumber,         T1.Date,         MIN(T2.Date) AS Date2,         DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiffFROM    YourTable T1        LEFT JOIN YourTable T2            ON T1.AccountNumber = T2.Accountnumber            AND T2.Date > T1.DateGROUP 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

you ca also use LAG analytical function to get the desired results as :

Suppose below is your input table:

id  account_number  account_date1     1001          9/10/20112     2001          9/1/20113     2001          9/3/20114     1001          9/12/20115     3001          9/18/20116     1001          9/20/2011select id,account_number,account_date,datediff(day,lag(account_date,1) over (partition by account_number order by account_date asc),account_date)as day_diffrencefrom yourtable;

Here is your output:

id  account_number  account_date    day_diffrence1     1001           9/10/2011    NULL4     1001           9/12/2011    26     1001           9/20/2011    82     2001           9/1/2011     NULL3     2001           9/3/2011     25     3001           9/18/2011    NULL

You can add a WHERE statement for the account number, if required. Your table is called t4

SELECT    t4.ID,    t4.AccountNumber,    t4.AcDate,    (SELECT TOP 1 AcDate     FROM t4 b     WHERE b.AccountNumber=t4.AccountNumber And b.AcDate>t4.AcDate     ORDER BY AcDate DESC, ID) AS NextDate,    [NextDate]-[AcDate] AS DiffFROM t4ORDER BY t4.AcDate;