连续行之间的日期差 [英] Date Difference between consecutive rows

问题描述

我有一个具有以下结构的表

I have a table with following structure

ID     Account Number     Date
1      1001               10/9/2011 (dd/mm/yyyy)
2      2001               1/9/2011 (dd/mm/yyyy)
3      2001               3/9/2011 (dd/mm/yyyy)
4      1001               12/9/2011 (dd/mm/yyyy)
5      3001               18/9/2011 (dd/mm/yyyy)
6      1001               20/9/2011 (dd/mm/yyyy)

基本上我想做的是有一个访问查询，该查询计算连续记录但相同帐号的日期差 预期的结果将是!!

Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number The expected result would be !!

1001      10/9/2011 - 12/9/2011     2 days
1001      12/9/2011 - 20/9/2011     8 days
1001      20/9/2011                 NA

基本上我想做的是有一个访问查询，该查询计算连续记录但对于相同帐号的日期差，在上面的示例中为1001.(日期不必在结果)

Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number , in the above example would be 1001. (the dates don't have to be shown in the result)

我使用Access 2003.

I use access 2003.

推荐答案

SELECT  T1.ID, 
        T1.AccountNumber, 
        T1.Date, 
        MIN(T2.Date) AS Date2, 
        DATEDIFF("D", 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;

或

SELECT  ID,
        AccountNumber,
        Date,
        NextDate,
        DATEDIFF("D", Date, NextDate)
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

这篇关于连续行之间的日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！