Date Difference between consecutive rows – complicated

calendar_today Asked Apr 11, 2012
thumb_up 5 upvotes
history Updated April 14, 2026

Direct Answer

Filtering out the irrelevant data With any complex query, part of the art is building up the query piece by piece, testing as you go. I'm assuming that the table name is…. This is an advisory response with reference links, ranked #37th of 67 by community upvote score, from 2012.


The Problem (Q-score 8, ranked #37th of 67 in the Access VBA archive)

The scenario as originally posted in 2012

I had previously posted a question which was answered but I need a query for this too.
I have a table structure with data like this (dates in the format dd/mm/yyyy).

ID    Account Number    Unit    Admit_Date    Disch_Date
1     1001              w32     01/04/2012    
2     1002              w32     01/04/2012    01/04/2012
3     1001              ccu     03/04/2012
4     1001              w33     05/04/2012
5     1003              cicu    04/04/2012
6     1001              ccu     07/04/2012
7     1001              ccu     07/04/2012    10/04/2012
8     1003              w33     05/04/2012
9     1003              w33     05/04/2012    08/04/2012

Basically this table deals with patients getting admitted to a particular ward and transferred between wards and then finally discharged either on same day or few days later.
The expected result from query would be:

Account_Number                                 No. Of Days
1001              01/04/2012    03/04/2012      2
1001              03/04/2012    05/04/2012      2
1001              05/03/2012    07/04/2012      2
1001              07/04/2012    10/04/2012      3
1002              01/04/2012    01/04/2012      0
1003              04/04/2012    05/04/2012      1
1003              05/04/2012    08/04/2012      3

The discharge date field will only be filled when the patient is discharged, hence I would like to calculate date difference between each date of movement of the patient including both admission and the date of discharge.

I use MS Access 2003.

I hope that some one will be able to help me with this.

Why community consensus is tight on this one

Across 67 Access VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.


The Verified Solution — niche answer (below median) (+5)

Advisory answer — community consensus with reference links

Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.

Filtering out the irrelevant data

With any complex query, part of the art is building up the query piece by piece, testing as you go.

I’m assuming that the table name is PatientMovements and that:

Given pairs of rows like ID = {6,7} and ID = {8,9}, it is correct to say that the row where the patient (account number), unit and admission date with null discharge date is ignored when there is also a record for the same patient, unit and admit date but a non-null discharge date.

So, step one is to generate the rows that we need to work on, filtering out the irrelevant data from the table recorded in the database. This is a UNION of two sets of data:

  1. Those rows with a non-null discharge date.
  2. Those rows with a null discharge date but no row for the same account, unit and admission date.

Clearly, the first part of the UNION is:

SELECT * FROM PatientMovements WHERE DischargeDate IS NOT NULL

Less obviously, the second part of the UNION is:

SELECT *
  FROM PatientMovements AS p1
 WHERE DischargeDate IS NULL
   AND NOT EXISTS
       (SELECT *
          FROM PatientMovements AS P2
         WHERE P1.Account   = P2.Account
           AND P1.Unit      = P2.Unit
           AND P1.AdmitDate = P2.AdmitDate
           AND P2.DischargeDate IS NOT NULL
       )

Now you can combine those into a single result set:

SELECT *
  FROM PatientMovements
 WHERE DischargeDate IS NOT NULL
UNION
SELECT *
  FROM PatientMovements AS p1
 WHERE DischargeDate IS NULL
   AND NOT EXISTS
       (SELECT *
          FROM PatientMovements AS P2
         WHERE P1.Account   = P2.Account
           AND P1.Unit      = P2.Unit
           AND P1.AdmitDate = P2.AdmitDate
           AND P2.DischargeDate IS NOT NULL
       )

You can verify the query above by checking that it returns rows with IDs 1..5, 7, and 9.

Warning: untested code. None of the SQL in this answer has been near a DBMS, so it is untested.

Applying Lessons Learned Previously

And then you can apply your learning from the other question to order the data and calculate the date differences, etc. The only complication is that you have to write that query out twice, which is painful (unless MS Access 2003 support the ‘WITH’ clause or common table expression).

But would there be no single query to obtain this required output?

The UNION is a single query, of course. I suppose you could just write:

SELECT *
  FROM PatientMovements
 WHERE (DischargeDate IS NOT NULL)
    OR (DischargeDate IS     NULL
        AND NOT EXISTS
            (SELECT *
               FROM PatientMovements AS P2
              WHERE P1.Account   = P2.Account
                AND P1.Unit      = P2.Unit
                AND P1.AdmitDate = P2.AdmitDate
                AND P2.DischargeDate IS NOT NULL
            )
       )

I can’t immediately think of a more compact way of doing the query.

Building the UNION into ‘The Other Answer’

The accepted answer to the other question has two possible solutions (as amended by comments and reformatted):

SELECT T1.ID, T1.AccountNumber, T1.Date, 
       MIN(T2.Date) AS NextDate, 
       DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
  FROM YourTable T1
  JOIN YourTable T2
    ON T1.AccountNumber = T2.AccountNumber AND T2.Date > T1.Date

Or:

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

As noted in a comment, the absence of the table name in the question leads to different table names appearing in the answer; what I called PatientMovements was called YourTable in this answer. The other difference is that the original question did not include the Unit or DischargeDate columns in the data. However, the UNION query I gave gives the relevant data on which to run these queries, so all that’s left to do is write the UNION query into the other answers in place of YourTable. This leads to:

SELECT T1.ID, T1.AccountNumber, T1.Date, 
       MIN(T2.Date) AS NextDate, 
       DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
  FROM (SELECT *
          FROM PatientMovements
         WHERE (DischargeDate IS NOT NULL)
            OR (DischargeDate IS     NULL
                AND NOT EXISTS
                    (SELECT *
                       FROM PatientMovements AS P2
                      WHERE P1.Account   = P2.Account
                        AND P1.Unit      = P2.Unit
                        AND P1.AdmitDate = P2.AdmitDate
                        AND P2.DischargeDate IS NOT NULL
                    )
               )
       ) AS T1
  JOIN (SELECT *
          FROM PatientMovements
         WHERE (DischargeDate IS NOT NULL)
            OR (DischargeDate IS     NULL
                AND NOT EXISTS
                    (SELECT *
                       FROM PatientMovements AS P2
                      WHERE P1.Account   = P2.Account
                        AND P1.Unit      = P2.Unit
                        AND P1.AdmitDate = P2.AdmitDate
                        AND P2.DischargeDate IS NOT NULL
                    )
               )
       ) AS T2
    ON T1.AccountNumber = T2.Accountnumber AND T2.Date > T1.Date

Or:

SELECT ID, AccountNumber, Date, NextDate,
       DATEDIFF("D", Date, NextDate) AS DaysDiff
  FROM (SELECT ID, AccountNumber, Date,
               (SELECT MIN(Date) 
                  FROM (SELECT *
                          FROM PatientMovements
                         WHERE (DischargeDate IS NOT NULL)
                            OR (DischargeDate IS     NULL
                                AND NOT EXISTS
                                    (SELECT *
                                       FROM PatientMovements AS P2
                                      WHERE P1.Account   = P2.Account
                                        AND P1.Unit      = P2.Unit
                                        AND P1.AdmitDate = P2.AdmitDate
                                        AND P2.DischargeDate IS NOT NULL
                                    )
                               )
                       ) AS T2
                 WHERE T2.Accountnumber = T1.AccountNumber
                   AND T2.Date > T1.Date
               ) AS NextDate
          FROM (SELECT *
                  FROM PatientMovements
                 WHERE (DischargeDate IS NOT NULL)
                    OR (DischargeDate IS     NULL
                        AND NOT EXISTS
                            (SELECT *
                               FROM PatientMovements AS P2
                              WHERE P1.Account   = P2.Account
                                AND P1.Unit      = P2.Unit
                                AND P1.AdmitDate = P2.AdmitDate
                                AND P2.DischargeDate IS NOT NULL
                            )
                       )
               ) AS T1
        ) AS T

So, as long as you are careful, and develop queries in fragments, and then combine them consistently, the most awful looking query can be tamed.

Common Table Expressions

Note that the SQL Standard has ‘common table expressions’ (CTEs) aka ‘WITH clauses’ which can make things still easier.

WITH YourTable AS
   (SELECT *
      FROM PatientMovements
     WHERE (DischargeDate IS NOT NULL)
        OR (DischargeDate IS     NULL
            AND NOT EXISTS
                (SELECT *
                   FROM PatientMovements AS P2
                  WHERE P1.Account   = P2.Account
                    AND P1.Unit      = P2.Unit
                    AND P1.AdmitDate = P2.AdmitDate
                    AND P2.DischargeDate IS NOT NULL
                )
           )
     )
SELECT T1.ID, T1.AccountNumber, T1.Date, 
       MIN(T2.Date) AS NextDate, 
       DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
  FROM YourTable T1
  JOIN YourTable T2
    ON T1.AccountNumber = T2.AccountNumber AND T2.Date > T1.Date

Or:

WITH YourTable AS
   (SELECT *
      FROM PatientMovements
     WHERE (DischargeDate IS NOT NULL)
        OR (DischargeDate IS     NULL
            AND NOT EXISTS
                (SELECT *
                   FROM PatientMovements AS P2
                  WHERE P1.Account   = P2.Account
                    AND P1.Unit      = P2.Unit
                    AND P1.AdmitDate = P2.AdmitDate
                    AND P2.DischargeDate IS NOT NULL
                )
           )
     )
SELECT ID, AccountNumber, Date, NextDate,
       DATEDIFF("D", Date, NextDate) AS DaysDiff
  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

One of the major advantages of using a CTE is that the optimizer is told explicitly that the table expressions is the same in all places it is used, whereas when it is written out several times, it might not spot that commonality. Plus, writing the query out several times opens up the possibility that the two ‘meant to be the same’ queries are actually slightly different because of an editing error; that possibility is precluded by the CTE. The other advantage in the current context was that combining the CTE with the solutions to the other question was child’s play.

Sadly for you, it is unlikely that MS Access 2003 supports CTEs. I share your pain; the DBMS I work with mainly doesn’t either.


When to Use It — vintage (14+ years old, pre-2013)

Ranked #37th in its category — specialized fit

This pattern sits in the 84% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.

What changed between 2012 and 2026

The answer is 14 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.

help
Frequently Asked Questions

This is a below-median answer — when does it still fit?
expand_more

Answer score +5 vs the Access VBA archive median ~4; this entry is niche. The score plus 8 supporting upvotes on the question itself (+8) means the asker and 4 subsequent voters all validated the approach.

This answer links out — what are the reference links worth following?
expand_more

Read the first external link for the canonical reference, then search this archive for a top-10 entry in the same category — advisory answers are best paired with a ranked code snippet to close the loop.

This answer is 14 years old. Is it still relevant in 2026?
expand_more

Published 2012, which is 14 year(s) before today’s Office 2026 build. The Access VBA object model has had no breaking changes in that window. Three things to re-test: (1) blocked macros on downloaded files (Mark-of-the-Web), (2) 64-bit API declarations (PtrSafe, LongPtr), (3) any shift toward Office Scripts for web scenarios.

Which Access VBA pattern ranks just above this one at #36?
expand_more

The pattern one rank above is “Select the first matching row”. If your use case overlaps, compare both before committing.

Data source: Community-verified Q&A snapshot. Q-score 8, Answer-score 5, original post 2012, ranked #37th of 67 in the Access VBA archive. Last regenerated April 14, 2026.

ms-access