Length of time between 2 database entries

[maggiethomas]

Helpdesk calls are logged in 2 tables, 'Calls' with 1 row per call, and
'CallActions' with a variable number of entries per call log. The schema for
CallActions is:

CallID, ActionID, datestamp, narrative

The Narrative can be made up of free-form text or just contain specific text
to show something has happened to the call, e.g. '**new', '**open',
'**investigating' etc. The CallID is unique in the 'Calls' table, the
ActionID is unique in the 'CallActions' table.

I need to pick out both '**New' and the first '**Open' actions for all
calls, and for each call calculate the time difference between them. There
could be multiple '**Open' actions for a single call which is why I need to
measure to the first one only. I'm not sure even where to start!

Maggie

 

[John Spencer]

Easiest way is to build a series of queries.
FirstQuery: Get just the New Actions
SecondQuery: Get "Open" actions and the earliest datestamp (Totals query
with CallId and Min of dateStamp)
Third Query; Use FirstQUery and SecondQuery joined on CallID to get data

SQL would look like
SELECT CallID, ActionID, DateStamp
FROM CallActions
WHERE Narrative ="**New"

SELECT CallID, Min(DateStamp) as FirstOpen
FROM CallActions
WHERE Narrative = "**Open"
GROUP BY CallID

SELECT T.CallID, T.ActionID, T.DateStamp, Ta.FirstOpen
, DateDiff("s",T.DateStamp,Ta.FirstOpen) as ElapsedSeconds
FROM FirstQuery as T INNER JOIN SecondQuery as Ta
ON T.CallID = Ta.CallID

You can do that all in one query as

SELECT T.CallID
, T.ActionID
, T.DateStamp
, Ta.FirstOpen
, DateDiff("s",T.DateStamp,Ta.FirstOpen) as ElapsedSeconds
FROM (
SELECT CallID, ActionID, DateStamp
FROM CallActions
WHERE Narrative ="**New" ) as T
INNER JOIN (
SELECT CallID, Min(DateStamp) as FirstOpen
FROM CallActions
WHERE Narrative = "**Open"
GROUP BY CallID) as Ta
ON T.CallID = Ta.CallID

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[maggiethomas]

John,

Thank you very much. I had only ever used subqueries in the 'select' part
of a statement, so your suggestion was a revelation to me - and also ran
instantly (my feeble query was taking over 20s!)