Design for elapsed time among pairs of rows?

J

J David Ellis

The table has a row for start date/time followed by a row
for stop date/time. The date and time are in different
columns. The data are imported from a tab-delimited text
file, the system event log, exported by the xp sp2 event
viewer. A query creates the table in question.

In a philosophical sense, in Access 03, what's the best way
to compute elapsed time, in minutes, for each pair of rows
in the table?

--David
 
K

Ken Sheridan

David:

I'd suggest that you first convert the two columns into a single column of
date/time data type, which you can do by adding a new column to the table and
using an update query to fill this column. If the two columns are already of
date/time data type you simply need to add the values, e.g.

UPDATE Events
SET EventDateTime =
EventDate + EventTime;

If the columns are text data type then you'll need to fill the column with
the results of an expression calling the CDate function. You'll need to
concatenate the values from the two columns as a single string expression on
which the CDate function can operate. Just how easy this will be depends on
the format of the two existing columns. If the formats are recognizable date
and times in the local date format or an internationally unambiguous format,
e.g. "01/02/2008" and " & "10:30:00" then its simply:

UPDATE Events
SET EventDateTime =
CDate(EventDate & " " & EventTime);

You can call the DateDiff function to give a difference in minutes. As the
values are in separate rows, however, you can't simply use an expression in a
query to return the time difference in a computed column of course. Is there
another column which identifies each pair of rows? If so then you can use
something like this, using a subquery as the third argument of the DateDiff
function:

SELECT EventID,
DATEDIFF( "n",MIN(EventDateTime),
(SELECT MAX(EventDateTime)
FROM Events as E2
WHERE E2.EventID = E1.EventID))
AS TimeDifference
FROM Events As E1
GROUP BY EventID;

Where Events is the table name, EventID is the column which identifies each
pair of rows with the start and stop date/time values, and EventDateTime is
the name of the column containing the date/time values.

You could of course work with the two columns as they are by using an
expression in the query to return a single date time value in place of the
new EventDateTime column, but I wouldn't favour that approach. The best
solution, of course, would be if your original query which builds the table
could insert the values as a single date/time column.

If the table does not have a column which identifies each pair of rows the
you can add one and fill it with pairs of values provided that the date/times
are sequential from start to end, using an update query like this:

UPDATE Events
SET EventID =
INT(DCOUNT("*", "Events",
"EventDateTime < #" &
FORMAT(EventDateTime,
"mm/dd/yyyy hh:nn:ss") & "#")
/2)+1;

Ken Sheridan
Stafford, England
 
J

J David Ellis

Thanks, Ken. You've given me more than I'd hoped for. I
appreciate your thorough and generous reply to this database
neophyte's question.

The date and time columns are date/time type. I may seek a
way to combine a pair of start/end records into one record.
It seems that would simplify the elapsed-time calculation.
But the log may not be clean: It's possible that not every
start-record has a matching end-record, for example. The
analysis is not yet finished.

BTW, I was born in Scarborough. Glad to hear from a fellow
Englishman.

--David (Fresno California)
 
K

Ken Sheridan

David:

If the rows aren't all paired there'll be no problem provided there is a way
you can identify each pair and each unpaired row. If there's a column
equivalent to EventID in my example, then its simple:

SELECT EventID,
DATEDIFF( "n",MIN(EventDateTime),
(SELECT MAX(EventDateTime)
FROM Events as E2
WHERE E2.EventID = E1.EventID))
AS TimeDifference
FROM Events As E1
GROUP BY EventID
HAVING COUNT(*) = 2;

This would simply ignore the unpaired rows.

SELECT EventID,
DATEDIFF( "n",MIN(EventDateTime),
(SELECT MAX(EventDateTime)
FROM Events as E2
WHERE E2.EventID = E1.EventID
GROUP BY EventID
HAVING COUNT(*) = 1))
AS TimeDifference
FROM Events As E1
GROUP BY EventID

This would return a Null elapsed time for each unpaired row.

Without an equivalent to EventID its possible that you might be able to use
the date/time data itself to identify the pairs and singletons, and thus fill
an EventID column, but that would depend on there being some pattern to the
data which would enable this.

Another strategy, which again depends on the presence of an EventID column
to identify the pairs, or the ability to fill one with an update query, would
be to add a StopDateTime column to the table and then fill it with:

UPDATE Events
SET StopDateTime =
DMAX("EventDateTime",
"Events","EventID = " & EventID);

You could then delete the redundant rows by running the following query:

DELETE *
FROM Events AS E1
WHERE EventdateTime = StopDateTime
AND (SELECT COUNT(*)
FROM Events As E2
WHERE E2.EventID = E1.EventID) = 2;

And rename the EventDateTime column by, either manually in table design
view or by adding the following function to a module in the database:

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

And calling it like so:

RenameColumn_DAO "Events","EventDateTime","StartDateTime"

This table is a better design as it now has separate columns for the start
and stop times, which is as it should be as these are separate attribute
types of the Events entity type. Computing the elapsed time is then simply a
case of subtracting the start from stop times with:

SELECT EventID,
DATEDIFF("n", StartDateTime, StopDateTime)
AS ElapsedTime;

BTW I know Scarborough quite well; one of my sons was a student at Hull
University's college there.

Ken Sheridan
Stafford, England
 
J

J David Ellis

Given the need for doing calculations among rows, instead of
columns, this problem is looking more like a spreadsheet
candidate. Thank you, Ken, for all your help. You've given
me an appreciation of Access query power. --David
 
Top