Calculate Datediff between rows in one column (wit

问题:

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;

回答1:

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]))

回答2:

when i treid this i am getting error like: Cannot find either column "Timelapse" or the user-defined function or aggregate "Timelapse.start_date_time", or the name is ambiguous.

i replcaed 'AllDays' with 'Timelapse'

SELECT
Timelapse.[request_id],
--Timelapse.[task_code],
Timelapse.[start_date_time]-->getting error at this column
(DateDiff(day,[PreviousDate],[start_date_time]), 0) AS Days_Between
FROM
    (
        SELECT
            [request_id],[task_code],
            [start_date_time],
            (
                SELECT Max([start_date_time])
                FROM [Monitor247].[dbo].[request_task] AS T2
                WHERE
                        T2.[request_id] = T1.[request_id]
                    AND T2.[start_date_time] < T1.[start_date_time]
            ) AS PreviousDate
        FROM [Monitor247].[dbo].[request_task] AS T1
    ) AS Timelapse;