How to Calculate the Date Difference Between Consecutive Rows in SQL?

Determining the Time Gap Between Sequential Records in SQL

This tutorial demonstrates how to compute the date difference between successive rows for a given account number within a SQL table.

Method 1: Subquery Approach

This query calculates the date difference using a subquery:

<code class="language-sql">SELECT  ID,
        AccountNumber,
        Date,
        NextDate,
        DATEDIFF(day, Date, NextDate) AS DateDifference
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</code>

Explanation:

The outer query selects the ID, AccountNumber, Date, calculated NextDate, and the date difference. The inner query identifies the next date (NextDate) for each row by finding the minimum date that's greater than the current row's date and belongs to the same account. DATEDIFF(day, Date, NextDate) calculates the difference in days.

Method 2: Self-Join Technique

A more efficient alternative utilizes a self-join:

<code class="language-sql">SELECT  T1.ID, 
        T1.AccountNumber, 
        T1.Date, 
        MIN(T2.Date) AS NextDate, 
        DATEDIFF(day, T1.Date, MIN(T2.Date)) AS DateDifference
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;</code>

This approach employs a LEFT JOIN to compare each row with subsequent rows having the same account number and a later date. MIN(T2.Date) finds the next date, and DATEDIFF(day, ...) computes the difference in days. Rows without a following row will have a NULL value for NextDate and DateDifference. Note that DATEDIFF's first argument specifies the unit of difference (here, 'day'). Adjust as needed (e.g., 'month', 'year').

The above is the detailed content of How to Calculate the Date Difference Between Consecutive Rows in SQL?. For more information, please follow other related articles on the PHP Chinese website!