在一列（超过2条记录）计算DATEDIFF行之间 [英] Calculate Datediff between rows in one column (with more than 2 records)

问题描述

我有日期，我想找到的区别的列的表。我已经能够找到离你的网站，让我两个日期之间的区别的答案，但无法找到任何东西，会给我每个客户的多个记录之间的差异。

我的数据看起来如下：

 客户Pstng_Date
亨利·05 \ 01 \ 2014年
亨利·10 \ 01 \ 2014年
亨利·15 \ 01/2014
威廉姆斯07 \ 01/2014
威廉姆斯15 \ 01 \ 2014年
 

我一直在使用下面的code，将计算威廉姆斯的例子，但不能让它工作，以便计算所有亨利的。我希望看到的是：

 客户Pstng_Date Days_Between
亨利·05 \ 01 \ 2014年0
亨利·10 \ 01 \ 2014年5
亨利·15 \ 01/2014 5
威廉姆斯07 \ 01/2014 0
威廉姆斯15 \ 01 \ 2014年8
 

我用下面的code来计算两个仅记录之间的差异已经。

  SELECT AllDays.ID，AllDays.Customer，AllDays.Pstng_Date，AllDays.NextDate，则DateDiff（D，[Pstng_Date]，[NextDate]）作为天
FROM（SELECT ID，客户，Pstng_Date，
                    （SELECT MIN（Pstng_Date）
                        从SAPData T2
                        WHERE T2.Pstng_Date＆GT; T1.Pstng_Date
                    ）AS NextDate
            从SAPData T1
        ）AS ALLDAYS;
 

解决方案

更​​改相关子查询，以便它返回的最大的早期 Pstng_Date 值 T1.Customer

该查询返回你希望在你的样本数据中获取什么样测试2007年

SELECT     AllDays.Customer，     AllDays.Pstng_Date，     新西兰（则DateDiff（D，[previousDate]，[Pstng_Date]），0）作为Days_Between 从     （         选择             顾客，             Pstng_Date，             （                 SELECT MAX（Pstng_Date）                 从SAPData为T2                 哪里                         T2.Customer = T1.Customer                     与T2.Pstng_Date＆LT; T1.Pstng_Date             ）AS previousDate         从SAPData为T1     ）AS ALLDAYS;

如果您将从一个Access会话之外运行的查询中， NZ（）功能将不可用。在这种情况下，你可以使用 IIF（） EX pression代替。

IIF（[previousDate]为NULL，0，则DateDiff（D，[previousDate] [Pstng_Date]））

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;

解决方案

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条记录）计算DATEDIFF行之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！