How to Calculate Date Differences Between Consecutive Rows in SQL?

SQL consecutive row date difference calculation

Determining the time interval between consecutive records is crucial for data analysis when working with large data sets. In this example, a SQL query is needed to calculate the date difference between consecutive rows with the same account number.

The following is a possible query:

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

This query uses LEFT JOIN to compare each row in table T1 (the original table) to all subsequent rows in table T2 that have the same account number. It then uses the DATEDIFF function to calculate the date difference and groups the results by ID, AccountNumber, and Date to remove duplicate rows. DATEDIFF(day, ...) Specifies the number of days to calculate the difference.

Another method is as follows:

<code class="language-sql">SELECT  ID,
        AccountNumber,
        Date,
        LEAD(Date, 1, NULL) OVER (PARTITION BY AccountNumber ORDER BY Date) AS NextDate,
        DATEDIFF(day, Date, LEAD(Date, 1, NULL) OVER (PARTITION BY AccountNumber ORDER BY Date)) AS DaysDiff
FROM    YourTable;</code>

This query uses window functions LEAD to get the next row date for each account number. LEAD(Date, 1, NULL) OVER (PARTITION BY AccountNumber ORDER BY Date) Get the date of the next row of the current row, or NULL if there is no next row. Then calculate the date difference and present the result in the required format. This method is generally more efficient than LEFT JOIN, especially on large datasets.

Both methods can calculate the date difference of consecutive rows. Which method to choose depends on your database system and performance requirements. The second method (using LEAD) is usually more concise and efficient.

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