Date Difference between consecutive rows

Question

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

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)

I use access 2003.

Longshoreman

Answer 1

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;

or

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

Murmuration

The second one worked perfectly , thanks. Is there a possibility that i can avoid the last one , which is 1000 20/9/2011 NA from showing up on the result... as this has no other date to compare with. Thanks again for the help. – Longshoreman

Add WHERE NextDate IS NOT NULL after AS T to the second query, or change the LEFT JOIN to an INNER JOIN on the top query. – Murmuration

Hi, I have posted another question which is a variant of the above requirement, Could you please have a look at it Thanks !! – Longshoreman

Is the reference to DateConsec in the innermost query of the second answer the same as YourTable in the next query outwards, and in the first answer? If not, what is it? – Caceres

Sorry, yes is is. It was the table I created to test it. I have changed this to your table to avoid future confusion. – Murmuration

I was looking for something just like this, however, I found one problem with this solution. It produces the date difference between a given date, and the earliest date in the set. How would you modify it to loop through dates, so we have datediff(d, date0, date1), datediff(d, date1, date2) instead of datediff(d, date0, date1), datediff(d, date0, date2)? – Passional

@Passional If you just want all the combinations then use a simple join (similar to the first solution). If you just want a sum of the datediff, then with the first solution you can change DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff to SUM(DATEDIFF("D", T1.Date, T2.Date)) AS DaysDiff - If this doesn't help then you probably need to ask a new question. – Murmuration

I managed to sort this using a different approach. The above wouldn't really work, as I have many, many dates, and joining them one by one would be rather clunky. But thank you for your answer! – Passional

This solution was the answer to hours of searching...with one slight adjustment. If you are using SQLIte then you will have to replace (in the second solution and similarly in the first) DATEDIFF("D",Date,NextDate) with (julianday(NextDate) - julianday(Date)) as difference_in_days as SQLite has no DateDiff function. – Antipasto

@Mohammed Rishal - Just an addition to previously good answers. I have posted my answer with the help of analytical function. – Gegenschein

Answer 2

you ca also use LAG analytical function to get the desired results as :

Suppose below is your input table:

id  account_number  account_date
1     1001          9/10/2011
2     2001          9/1/2011
3     2001          9/3/2011
4     1001          9/12/2011
5     3001          9/18/2011
6     1001          9/20/2011


select id,account_number,account_date,
datediff(day,lag(account_date,1) over (partition by account_number order by account_date asc),account_date)
as day_diffrence
from yourtable;

Here is your output:

id  account_number  account_date    day_diffrence
1     1001           9/10/2011    NULL
4     1001           9/12/2011    2
6     1001           9/20/2011    8
2     2001           9/1/2011     NULL
3     2001           9/3/2011     2
5     3001           9/18/2011    NULL

Gegenschein

The question is tagged with MS Access, and as far as I know Access does not support analytical functions. The question also states the OP is using Access 2003 which definitely does not support the LAG() function. So while this is a good approach for other DBMS, this is not an answer to this question. Also, the OP wants the difference between the current row and the next date, so you would need to use LEAD() rather than LAG() – Murmuration

@GarethD-- oops. I didn't noticed that. Thanks :-) – Gegenschein

Answer 3

You can add a WHERE statement for the account number, if required. Your table is called t4

SELECT 
   t4.ID, 
   t4.AccountNumber, 
   t4.AcDate, 
   (SELECT TOP 1 AcDate 
    FROM t4 b 
    WHERE b.AccountNumber=t4.AccountNumber And b.AcDate>t4.AcDate 
    ORDER BY AcDate DESC, ID) AS NextDate, 
   [NextDate]-[AcDate] AS Diff
FROM t4
ORDER BY t4.AcDate;

Vincenza

Answer 4

try this:

select [Account Number], DATEDIFF(DD, min(date), max(date)) as dif
from your_table
group by [Account Number]

Valadez

true. did not realize the ID was a key. I thought it was part of the account somehow. thanks – Valadez

@Valadez : i think the query you have provided will only provide date difference between first and last date and not between all the dates !!!! correct ? – Longshoreman

difference on each account number. isn't that what you needded? – Valadez

Answer 5

GarethD's answer worked for me perfectly.

FYI: When you need ORDER BY clause, please use it at the end of SELECT query in the root.

SELECT  ConsignorID,
            DateRequired StartDate,
            NextDate,
            DATEDIFF("D", DateRequired, NextDate)
FROM (  SELECT  ConsignorID,
                DateRequired,
                (SELECT MIN(DateRequired) 
                 FROM "TABLENAME" T2
                 WHERE T2.DateRequired > T1.DateRequired
                ) AS NextDate
            FROM "TABLENAME" T1
        ) AS T

ORDER BY T.DateRequired ASC

Ankeny

Answer 6

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

Dealt