Calculate the time interval

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have a data set like the following
PersonID EncounterDate
1 Sep 1, 04
1 Sep 4, 04
2 Aug 2, 03
2 Jan 1, 03
2 Feb 9, 04
....
I want to calculate the time interval between each two encounters for each
person.
Some people only has one interval, but some may have several intervals. Is
that possible one query can handle it or I should other methods?
Thanks in advance!
 
You can use a subquery within a query to return the previous value.
Something like this should get you started (not tested) -- note that this
assumes that EncounterDate is a date/time field:

SELECT T.PersonID, T.EncounterDate, (DateDiff("d", (SELECT
Max(X.EncounterDate) AS PrevDate FROM TableName AS X WHERE X.PersonID =
T.PersonID AND X.EncounterDate < T.EncounterDate), T.EncounterDate)) AS
DateInterval FROM TableName AS T ORDER BY T.PersonID, T.EncounterDate DESC;
 
Back
Top