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:
- Those rows with a non-null discharge date.
- 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.
