Calculate Span Between Dates

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

Guest

I have a table with the following:
EventID=number
EventName-text
EventDate=date
and some other fields

I desire to craft a query which will return a list of these events with the
span of time between each.

For example:
EventID1, EventName1, EventID2, EventName2, span of time (in days) between
the two
EventID2, EventName2, EventID3, EventName3, span of time (in days) between
the two
EventID3, EventName3, EventID4, EventName4, span of time (in days) between
the two
etc.

I have been unsuccessful in my attempts at this. I would be appreciative of
some assistance.

Thank you.
 
Shoelaces said:
I have a table with the following:
EventID=number
EventName-text
EventDate=date
and some other fields

I desire to craft a query which will return a list of these events with the
span of time between each.

For example:
EventID1, EventName1, EventID2, EventName2, span of time (in days) between
the two
EventID2, EventName2, EventID3, EventName3, span of time (in days) between
the two
EventID3, EventName3, EventID4, EventName4, span of time (in days) between
the two
etc.

I have been unsuccessful in my attempts at this. I would be appreciative of
some assistance.
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (untested):

SELECT EventID, EventName, EventDate,

(SELECT MAX(EventDate) FROM table1 WHERE EventDate < T.EventDate) as
PrevDate,

DateDiff("d", (SELECT Max(EventDate) FROM table1
WHERE EventDate < T.EventDate),
EventDate) As DaysBetweenEvents

FROM table1 As T

You can also use DMax(), but I prefer subqueries. Sometimes queries for
reports don't "like" subqueries in SELECT clauses, that's when I use the
domain aggregate functions.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQedRQIechKqOuFEgEQK4fACgipYsxU+HTUGTzGY+T0w4DuXeK/EAoNTy
7uBE1f266YYiGEsVK8aS0Asx
=CN0O
-----END PGP SIGNATURE-----
 
Back
Top