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.

GarethD · Accepted Answer · 2013-03-28T10:53:40.060

66

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

edited Mar 28 '13 at 10:53

answered Apr 03 '12 at 14:23

GarethD

68,045
10
83
123

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. – Mohammed Rishal Apr 04 '12 at 01:43
1

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. – GarethD Apr 04 '12 at 06:25
Hi, I have posted another question which is a variant of the above requirement, Could you please have a look at it Thanks !! – Mohammed Rishal Apr 11 '12 at 01:08
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? – Jonathan Leffler Apr 15 '12 at 00:24
Sorry, yes is is. It was the table I created to test it. I have changed this to your table to avoid future confusion. – GarethD Apr 15 '12 at 15:37
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)? – branwen85 Jun 30 '15 at 15:11
@branwen85 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. – GarethD Jun 30 '15 at 16:07
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! – branwen85 Jul 01 '15 at 12:33
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. – globalSchmidt Nov 23 '16 at 05:10
@Mohammed Rishal - Just an addition to previously good answers. I have posted my answer with the help of analytical function. – vikrant rana May 23 '19 at 11:42

score 19 · Answer 2 · answered May 23 '19 at 11:38

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

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()` — GarethD, May 23 '19 at 13:12

score 2 · Answer 3 · answered Apr 03 '12 at 14:16

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;

Jimmy · Answer 4 · 2017-06-23T10:21:39.170

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

Diego · Answer 5 · 2012-04-03T14:46:48.317

0

try this:

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

edited Apr 03 '12 at 14:46

answered Apr 03 '12 at 14:09

Diego

34,802
21
91
134

true. did not realize the ID was a key. I thought it was part of the account somehow. thanks – Diego Apr 03 '12 at 14:47
@Diego : i think the query you have provided will only provide date difference between first and last date and not between all the dates !!!! correct ? – Mohammed Rishal Apr 04 '12 at 02:31
difference on each account number. isn't that what you needded? – Diego Apr 04 '12 at 08:07

score -1 · Answer 6 · edited Aug 08 '15 at 16:14

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

Date Difference between consecutive rows

6 Answers6

Linked

Related