Time elapsed between records

[jclaibor]

Hi I have a query that looks at a detailed transaction list that includes
account numbers, transaction time/date, amount of transaction etc. I need to
find out the time elapsed between transaction records with the same account
number. Since there are hundreds of possible locations times can overlap
between locations but not at the same location. For instance I might have 30
transactions at exactly 1PM on 1/12/08 but I only have one per location at a
time. I need to see the frequency and time lapse between like account
numbers accross all of the locations. Any help would be appreciated.

thanks

 

[Michel Walsh]

Something like:


SELECT a.transactionID, a.locationID,
LAST(a.transactionStart), LAST(a.transactionEnd),
LAST(a.transactionStart) - Nz(MAX(b.transactionEnd),
LAST(a.transactionStart)) AS elapsedTimeSincePreviousTransactionSameLocation

FROM myTable As a LEFT JOIN myTable As b
ON a.locationID=b.locationID
AND a.transactionStart > b.transactionEnd

GROUP BY a.transactionID, a.locationID


I assumed there is NO overlap at any given location, considering ONLY the
said location.


Basically, for any given {transactionID, locationID}, alias 'a', I look at
all transactions occurring at the same location, but before, with alias 'b'
: ON a.locationID=b.locationID AND a.transactionStart > b.transactionEnd.
So, since there is no overlap at a given location, the MAX(b.transactionEnd)
is the date_time preceding the actual a.transactionStart. Now, final little
touch, the first transaction to occur, at a given location, has no
'previous' transaction, so I used a left join to keep it, the 'b' fields,
after the join, for that a.transaction, will all be NULL. The NZ will change
the MAX(b.transactionEnd), a null, into the actual a.transactionStart, to
make an elapsed time of zero. Note that without the NZ logic, that would
have result to a NULL, which is also acceptable, after all:


SELECT a.transactionID, a.locationID,
LAST(a.transactionStart), LAST(a.transactionEnd),
LAST(a.transactionStart) -MAX(b.transactionEnd) AS
elapsedTimeSincePreviousTransactionSameLocation

FROM myTable As a LEFT JOIN myTable As b
ON a.locationID=b.locationID
AND a.transactionStart > b.transactionEnd

GROUP BY a.transactionID, a.locationID

or:

SELECT a.transactionID, a.locationID,
LAST(a.transactionStart), LAST(a.transactionEnd),
Nz(LAST(a.transactionStart) -MAX(b.transactionEnd), 0) AS
elapsedTimeSincePreviousTransactionSameLocation

FROM myTable As a LEFT JOIN myTable As b
ON a.locationID=b.locationID
AND a.transactionStart > b.transactionEnd

GROUP BY a.transactionID, a.locationID
As usual, I used LAST to pump other fields having their values uniquely
defined, given the GROUPs.


but the first formulation may be more instructive, even if it appears more
complex, at first glance.




Hoping it may help,
Vanderghast, Access MVP