Calculate Datediff between rows in one column (with more than 2 records)

database, ms-access, sql, vba

Solution

Change the correlated subquery so that it returns the greatest of the earlier `Pstng_Date` values for `T1.Customer`

This query returned what you want when tested with your sample data in Access 2007.

SELECT
    AllDays.Customer,
    AllDays.Pstng_Date,
    Nz(DateDiff("d",[PreviousDate],[Pstng_Date]), 0) AS Days_Between
FROM
    (
        SELECT
            Customer,
            Pstng_Date,
            (
                SELECT Max(Pstng_Date)
                FROM SAPData AS T2
                WHERE
                        T2.Customer = T1.Customer
                    AND T2.Pstng_Date < T1.Pstng_Date
            ) AS PreviousDate
        FROM SAPData AS T1
    ) AS AllDays;

If you will be running the query from outside an Access session, the `Nz()` function will not be available. In that case, you could use an `IIf()` expression instead.

IIf([PreviousDate] Is Null, 0, DateDiff("d",[PreviousDate],[Pstng_Date]))

Problem

I have a table which has a column of dates which I want to find the difference between. I have been able to find off your site an answer that gets me the difference between two dates, but can't find anything that would give me the difference between several records per customer. My data looks as below: ``` Customer Pstng_Date Henry 05\01\2014 Henry 10\01\2014 Henry 15\01/2014 Williams 07\01/2014 Williams 15\01\2014 ``` I have been using the below code which will calculate the Williams example, but can't get it work so that it calculates all the Henry ones. What I want to see is: ``` Customer Pstng_Date Days_Between Henry 05\01\2014 0 Henry 10\01\2014 5 Henry 15\01/2014 5 Williams 07\01/2014 0 Williams 15\01\2014 8 ``` I have been using the below code to calculate the difference between two records only. ``` SELECT AllDays.ID, AllDays.Customer, AllDays.Pstng_Date, AllDays.NextDate, DateDiff("d",[Pstng_Date],[NextDate]) AS Days FROM (SELECT ID, Customer, Pstng_Date, ( SELECT Min(Pstng_Date) FROM SAPData T2 WHERE T2.Pstng_Date > T1.Pstng_Date ) AS NextDate FROM SAPData T1 ) AS AllDays; ```

Original source