calculating time differences between consecutive records

[cterner]

I have a large database that consists of transaction event histories
(i.e. records when a page was viewed, on which PC, each with a
time/date record). I'd like to calculate elapsed time for each page
view by calculating the difference between the time/date of each record
and the subsequent record for a given PC.
Is there a simple means to accomplish this?
A related question is, is there a simple means to add a record number
column to an existing database. I'm thinking that I could create a
second copy of the database that has been sorted by PC ID and
time/date, subtract one from each record number, do a join then
calculate the difference.
I have a very, very rudimentary understanding a ACCESS and databases,
so things like Visual Basic solution are beyond me.

[Ken Sheridan]

If the time differences are all going to be less than 24 hours use a query
like the following, which uses a subquery to get the previous date/time:

SELECT SomeField, SomeOtherField, YourDateTimeField,
FORMAT(YourDateTimeField -
NZ((SELECT MAX(YourDateTimeField)
FROM YourTable AS T2
WHERE T2.YourDateTimeField <T1.YourDateTimeField),0),"hh:nn:ss")
AS TimeDifference
FROM YourTable AS T1
ORDER BY YourDateTimeField DESC;

If the differences could be more than 24 hours:

SELECT SomeField, SomeOtherField, YourDateTimeField,
NZ(INT(YourDateTimeField -
(SELECT MAX(YourDateTimeField)
FROM YourTable AS T2
WHERE T2.YourDateTimeField <T1.YourDateTimeField)),0) & ":" &
FORMAT(YourDateTimeField -
NZ((SELECT MAX(YourDateTimeField)
FROM YourTable AS T2
WHERE T2.YourDateTimeField <T1.YourDateTimeField),0),"hh:nn:ss")
AS TimeDifference
FROM YourTable AS T1
ORDER BY YourDateTimeField DESC;

These will return the time differences in the format hours:minutes:seconds
or days:hours:minutes:seconds in the latter case. Just open the query
designer, but don't add a table. Switch to SQL view and paste whichever of
the above is appropriate onto the window in place of what's there. then
change the table and field names to your own. You can add any other fields
from the table in the first line if necessary.

Ken Sheridan
Stafford, England

[cterner]

Thanks.
I think I now have a CPU processing limitation. ACCESS times out when
I try to run this. I'm dealing with 120,000 records and was told that
what I'm trying to do is not practical on my M-series Pentium
processor.

[cterner]

Thanks.
I think I now have a CPU processing limitation. ACCESS times out when
I try to run this. I'm dealing with 120,000 records and was told that
what I'm trying to do is not practical on my M-series Pentium
processor.

[Ken Sheridan]

Your table is not particularly large as tables go. Is the date/time column
indexed? If not, that might well affect performance, so index it if
necessary.

Another approach would be:

SELECT T1.SomeField, T1.SomeOtherField,
FORMAT(T1.YourDateTimeField - MAX(T2.YourDateTimeField) ),"hh:nn:ss")
As TimeDifference
FROM YourTable As T1, YourTable AS T2
WHERE T1. YourDateTimeField > T2. YourDateTimeField
GROUP BY T1.SomeField, T1.SomeOtherField, T1.YourDateTimeField
ORDER BY T1. YourDateTimeField DESC;

The result set of this would differ from that of the former query I sent you
in that the row with the earliest date/time value would not be returned. In
the first this row would be returned with a time difference of zero. This
query, by joining two instances of the table on T1. YourDateTimeField > T2.
YourDateTimeField excludes that row as there is no row with a lower dat/time
value to join to of course.

If you don't need the result set ordered by descending date/time value leave
out the ORDER BY clause, which will help performance.

If the query is the RecordSource of a report you can order the rows in the
report by means of its internal sorting and grouping mechanism. An ORDER BY
clause in a query is generally ignored by a report anyway and will merely
slow things down; not invariably as is sometimes said, but its best to assume
it will be. You could also omit the FORMAT function call from the query and
format the TimeDifference control in the report.

[cterner]

This works! So now my question is how to I do this where there are
different and unrelated groups of records?

I'm evaluating PageView times on multiple PCs. Each new view is
recorded with PC ID, PageName and ViewTime/Date. So view time will be
the difference between successive records.

Is there a grouping command that would manage this?

[Ken Sheridan]

You should be able to include the PC ID column in the SELECT, WHERE, GROUP
BY and ORDER BY clauses:

SELECT T1.[PC ID], T1.[ViewTime/Date],
FORMAT(T1.[ViewTime/Date] - MAX(T2.[ViewTime/Date]) ),"hh:nn:ss")
AS TimeDifference

FROM YourTable As T1, YourTable AS T2

WHERE T1.[PC ID] = T2.[PC ID]
AND T1.[ViewTime/Date] > T2.[ViewTime/Date]
GROUP BY T1.[PC ID], T1.[ViewTime/Date]
ORDER BY T1.[PC ID], T1.[ViewTime/Date] DESC;

If you want to return the Page name column as well then join the table to
this query on the [PC ID] and [ViewTime/Date] columns.

[cterner]

This is terrific! I very much appreciate your help.
I'm thinking I'm close to what I need.

Here's my SQL: note: "RoomNumber" is my "PC ID"
SELECT T1.RoomNumber, T1.DateTime,
Format(([T1].[DateTime]-Max([T2].[DateTime])),"hh:nn:ss") AS
ElapsedTimeSec
FROM NewPageView AS T1, NewPageView AS T2
WHERE (((T1.RoomNumber)=[T2].[RoomNumber]) AND
((T2.DateTime)<[T1].[DateTime]))
GROUP BY T1.RoomNumber, T1.DateTime
ORDER BY T1.RoomNumber, T1.DateTime;

RoomNumber DateTime ElapsedTimeSec
300-1 8/28/2006 11:35:24 AM 21:19:48
300-1 8/28/2006 3:32:04 PM 03:56:40
300-1 8/29/2006 7:39:09 PM 04:07:06
300-1 8/30/2006 7:35:50 AM 11:56:41
300-2 6/1/2006 12:51:49 PM 04:26:04
300-2 6/1/2006 12:58:22 PM 00:06:33
300-2 6/1/2006 1:02:16 PM 00:03:55
300-2 6/1/2006 1:44:17 PM 00:42:00

Here's the related records from NewPageView table:

RoomNumber DateTime PageName PageTitle Template
300-1 8/28/2006 3:32:04 PM TV TV TV
300-1 8/29/2006 7:39:09 PM TV TV TV
300-1 8/30/2006 7:35:50 AM TV TV TV
300-2 6/1/2006 8:25:45 AM TV TV TV
300-2 6/1/2006 12:51:49 PM TV TV TV
300-2 6/1/2006 12:58:22 PM Primary Channel Guide Channel Guide Channel
Guide
300-2 6/1/2006 1:02:16 PM TV TV TV
300-2 6/1/2006 1:44:17 PM TV TV TV
300-2 6/1/2006 6:04:31 PM TV TV TV

So, a few things. I'd like to have the calculated ElapsedTime
associated with the record for the starting DateTime vs. the ending
DateTime record. This was the original arrangement you had suggested.
Do I simply add the WHERE and GROUP BY statements:

WHERE (((T1.RoomNumber)=[T2].[RoomNumber]) AND
((T2.DateTime)<[T1].[DateTime]))
GROUP BY T1.RoomNumber, T1.DateTime

You also had introduced the second Table T2 (not included in the
original FROM statement).
FROM NewPageView AS T1, NewPageView AS T2
Does this do anything? I don't understand why you didn't need it in
the original but used it in the latest.

Also, in the current query, is it correct to assume that the first
ElapsedTime for each RoomNumber grouping is calculated with a start
DateTime = 0 (or 00:00:00)? If I eliminated the FORMAT function then
multiplied ElapsedTime result by 24*60*60 will I get the time
difference in seconds (which is what I really need)

If switch it to having the ElapsedTime associated with the StartTime,
I'm thinking this will cause a problem with the calculation of the
ElapsedTime for the final record in each RoomNumber grouping (maybe
negative). I'd like such records' ElapsedTime to indicate "EOR" (End
Of Record).

Thanks!

[Ken Sheridan]

Taking your points one by one:

>> I'd like to have the calculated ElapsedTime
>> associated with the record for the starting DateTime vs. the ending
>> DateTime record. This was the original arrangement you had suggested.

You should just need to include the MAX(T2DateTime) in the SELECT clause as
a column as well as including it in the expression to compute the elapsed
time. I've included both start and end below, but you can remove the start
time if you wish.

SELECT T1.RoomNumber,
MAX(T2.DateTime) AS StartTime,
T1.DateTime AS EndTime,
Format(([T1].[DateTime]-MAX([T2].[DateTime])),"hh:nn:ss") AS

ElapsedTimeSec
FROM NewPageView AS T1, NewPageView AS T2
WHERE (((T1.RoomNumber)=[T2].[RoomNumber]) AND
((T2.DateTime)<[T1].[DateTime]))
GROUP BY T1.RoomNumber, T1.DateTime
ORDER BY T1.RoomNumber, T1.DateTime;

>> You also had introduced the second Table T2 (not included in the

>> original FROM statement).
>> FROM NewPageView AS T1, NewPageView AS T2
>> Does this do anything? I don't understand why you didn't need it in
>> the original but used it in the latest.

The original solution used a subquery to get the start time, whereas the
second solution joined two instances of the table. The join is done in the
WHERE clause rather than a JOIN clause, but it’s a join nevertheless. Prior
to the SQL 92 standard this was the only way to join tables. The JOIN clause
was actually introduced to cater for outer joins, which can't be done in the
WHERE clause. To differentiate between each instance the table is given the
Aliases T1 and T2. At the time I didn't know your table name, but the usual
convention for aliases is to use the initial(s) of the table name, so in your
case I'd probably have used something like NPV1 and NPV2.

>> Also, in the current query, is it correct to assume that the first
>> ElapsedTime for each RoomNumber grouping is calculated with a start
>> DateTime = 0 (or 00:00:00)? If I eliminated the FORMAT function then
>> multiplied ElapsedTime result by 24*60*60 will I get the time
>> difference in seconds (which is what I really need)

You would; date/time values are implemented in Access as 64 bit floating
point number of which the integer part represents the days and the fractional
part the times of day (day zero being 30 December 1899). You can also use
the DATEDIFF function; this allows you to specify the time unit in which you
want the difference between two date/time values.

SELECT T1.RoomNumber,
MAX(T2.DateTime) AS StartTime,
T1.DateTime AS EndTime,
DATEDIFF("s", MAX(T2.DateTime), T1.DateTime) AS ElapsedTimeSec

FROM NewPageView AS T1, NewPageView AS T2
WHERE (((T1.RoomNumber)=[T2].[RoomNumber]) AND
((T2.DateTime)<[T1].[DateTime]))
GROUP BY T1.RoomNumber, T1.DateTime
ORDER BY T1.RoomNumber, T1.DateTime;

>> If switch it to having the ElapsedTime associated with the StartTime,

>> I'm thinking this will cause a problem with the calculation of the
>> ElapsedTime for the final record in each RoomNumber grouping (maybe
>> negative). I'd like such records' ElapsedTime to indicate "EOR" (End
>> Of Record).

Doing it the above way would not give you the final time as a start time at
all, only as an end time. If you want the last time included as a start time
it might be possible using an outer join, along with the NZ function to
return "EOR" if the DATEDIFF function returns a NULL:

SELECT T2.RoomNumber,
MAX(T2.DateTime) AS StartTime,
T1.DateTime AS EndTime,
NZ(DATEDIFF("s", MAX(T2.DateTime), T1.DateTime),"EOR") AS ElapsedTimeSec
FROM NewPageView AS T1 RIGHT JOIN NewPageView AS T2
ON T1.RoomNumber = T2.RoomNumber
AND T2.DateTime < T1.DateTime
GROUP BY T2.RoomNumber, T1.DateTime
ORDER BY T2.RoomNumber, T1.DateTime;

[cterner]

this works great!

My last 2 questions, hopefully...

The ElapsedTime Query results with 9 fewer records (128978 vs. 128987)
than from the original PageView table, from which it is based. I
cannot identify the 9 records in question and don't know if I should be
concerned. Statistically I can live with a 0.01% error, but could this
be a symptom of another more signficiant problem?

As far as performance, it does take about 20 min to run the query
despite indexing the PageView table on both RoomNumber and DateTime. I
also deleted the ORDER BY statement thinking it might speed things up
(I don't need results in any particular order). Is there anything I
can do to improve response time? If not, can I at least convert the
ElapsedTime Query into a "Make Table" query. The reason being is I
have another query that includes a JOIN of ElapsedTime and PageView as
well as a few other smaller tables, which takes over 30 min to run. I
will be using this design on potentially much larger record sets and am
concerned about how long these queries will take, or if they will work
at all.

[Ken Sheridan]

The only reasons I can think of for the missing rows are:

1. If you not using the version with the outer join one row per room will
be dropped because the earliest row per room has no earlier one to join to.

2. There are rooms which have only one row in the table.

3. A combination of both of these.

You should be able to identify which of the rows are being dropped by LEFT
JOINing the original table to the query on the DateTime column from the table
and the EndTime column from the query WHERE EndTime IS NULL.

As far as performance is concerned I wouldn't expect it to be lightning
fast, but am a little surprised its so slow. An Access project (.adp file)
might well be worth considering if you are going to be using larger sets.

There's no reason why you could not create a table from the query's result
set, but a better option is to set up an empty table first and then append
the rows returned by the query to it. It might even be worth doing this room
number by room number by executing the SQL statement in a loop through a
recordset of the distinct room numbers.

[cterner]

I'm getting unexplainable duplicate records when I join the "make
table" query the earlier advice helped me create, with other much
smaller tables. Is this a "data" (i.e. record peculiar) problem or a
query statement problem. Any clue as to where to look for the problem
is welcomed.
The following is the query that produces the duplicates.

I've also included sample records from the make-table query followed by
the results of the query that used the initial query and produced
duplicates.

SELECT PageView.RecordNumber, PageView.RoomNumber, PageView.DateTime,
[ElapsedTime Query].ElapsedTimeSec, IIf(IsNull([ElapsedTime
Query]!EndTime),"Include",IIf(([ElapsedTimeSec]<2 And [ScrnTypeCat]
Like "*Video*"),"Exclude","Include")) AS [Exclude (<2 Sec & Video)],
PageView.PageName, PageView.PageTitle, PageView.Template,
PageCat.ContentCat, PageCat.ScrnTypeCat, PageCat.LanguageCat,
Location.Location, IIf(IsNull([ElapsedTime
Query]!EndTime),"EOR",IIf([ElapsedTimeSec]<5,"less than
5",IIf([ElapsedTimeSec]<60,"less than 60
s",IIf([ElapsedTimeSec]<600,"less than 10
m",IIf([ElapsedTimeSec]<3600,"less than 1 h","Greater than 1 h"))))) AS
ElapsedTimeGrouping, IIf([ScrnTypeCat] Like "*Video*","Video","Not
Video") AS Video, DatePart("m",PageView!DateTime) AS CreateMonth,
DatePart("d",PageView!DateTime) AS CreateDay INTO [PageViewQuery Table]
FROM (PageCat RIGHT JOIN (Location RIGHT JOIN PageView ON
Location.RoomNumber = PageView.RoomNumber) ON PageCat.PageName =
PageView.PageName) LEFT JOIN [ElapsedTime Query] ON
(PageView.RoomNumber = [ElapsedTime Query].RoomNumber) AND
(PageView.DateTime = [ElapsedTime Query].StartTime)
ORDER BY PageView.RecordNumber;

RecordNumber RoomNumber DateTime PageName PageTitle Template
57 000-0 7/10/2006 10:12:29 PM Patient / Guest Services.graphic
menu Patient/Guest Services Graphic Menu

RecordNumber RoomNumber DateTime ElapsedTimeSec Exclude (<2 Sec &
Video) PageName PageTitle Template ContentCat ScrnTypeCat LanguageCat Location ElapsedTimeGrouping Video CreateMonth CreateDay
57 000-0 7/10/2006 10:12:29 PM 3.07700000703335 Include Patient / Guest
Services.graphic menu Patient/Guest Services Graphic
Menu Hospital-Patient Services Navigation English Test less than 5 Not
Video 7 10
57 000-0 7/10/2006 10:12:29 PM 3.07700000703335 Include Patient / Guest
Services.graphic menu Patient/Guest Services Graphic
Menu Hospital-Patient Services Navigation English Test less than 5 Not
Video 7 10

RecordNumber RoomNumber DateTime PageName PageTitle Template
137 000-0 7/21/2006 7:49:08 AM Patient Education Videos.sub
menu Patient Education Videos Sub Menu

RecordNumber RoomNumber DateTime ElapsedTimeSec Exclude (<2 Sec &
Video) PageName PageTitle Template ContentCat ScrnTypeCat LanguageCat Location ElapsedTimeGrouping Video CreateMonth CreateDay
137 000-0 7/21/2006 7:49:08 AM 12669.0469998401 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test Greater than 1 h Not Video 7 21
137 000-0 7/21/2006 7:49:08 AM 12669.0469998401 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test Greater than 1 h Not Video 7 21

RecordNumber RoomNumber DateTime PageName PageTitle Template
154 000-0 7/25/2006 11:24:59 AM Patient Education Videos.sub
menu Patient Education Videos Sub Menu
155 000-0 7/25/2006 11:34:07 AM Patient Education Videos.sub
menu Patient Education Videos Sub Menu
156 000-0 7/25/2006 11:34:33 AM Congestive Heart Failure.health
video Congestive Heart Failure Health Video
157 000-0 7/25/2006 11:34:39 AM Patient Education Videos.sub
menu Patient Education Videos Sub Menu

RecordNumber RoomNumber DateTime ElapsedTimeSec Exclude (<2 Sec &
Video) PageName PageTitle Template ContentCat ScrnTypeCat LanguageCat Location ElapsedTimeGrouping Video CreateMonth CreateDay
154 000-0 7/25/2006 11:24:59 AM 548.097000061534 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 10 m Not Video 7 25
154 000-0 7/25/2006 11:24:59 AM 548.097000061534 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 10 m Not Video 7 25
155 000-0 7/25/2006 11:34:07 AM 25.5300000775605 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 60 s Not Video 7 25
155 000-0 7/25/2006 11:34:07 AM 25.5300000775605 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 60 s Not Video 7 25
156 000-0 7/25/2006 11:34:33 AM 6.48300019092858 Include Congestive
Heart Failure.health video Congestive Heart Failure Health
Video Education Content-Patient Education Video English Test less than
60 s Video 7 25
157 000-0 7/25/2006 11:34:39 AM 10.6740003917366 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 60 s Not Video 7 25
157 000-0 7/25/2006 11:34:39 AM 10.6740003917366 Include Patient
Education Videos.sub menu Patient Education Videos Sub
Menu Education Navigation English Test less than 60 s Not Video 7 25

RecordNumber RoomNumber DateTime PageName PageTitle Template
83840 523-1 8/20/2006 9:37:33 AM Welcome Video.health video Welcome
Video Health Video
83841 523-1 8/20/2006 9:37:33 AM Welcome Video.health video Welcome
Video Health Video

RecordNumber RoomNumber DateTime ElapsedTimeSec Exclude (<2 Sec &
Video) PageName PageTitle Template ContentCat ScrnTypeCat LanguageCat Location ElapsedTimeGrouping Video CreateMonth CreateDay
83840 523-1 8/20/2006 9:37:33 AM 0.17299996688962 Include Welcome
Video.health video Welcome Video Health Video Hospital-General
Information Content-Static Text English 5th Floor less than 5 Not
Video 8 20
83840 523-1 8/20/2006 9:37:33 AM 0.17299996688962 Include Welcome
Video.health video Welcome Video Health Video Hospital-General
Information Content-Static Text English 5th Floor less than 5 Not
Video 8 20
83841 523-1 8/20/2006 9:37:33 AM 6.97000029031187 Include Welcome
Video.health video Welcome Video Health Video Hospital-General
Information Content-Static Text English 5th Floor less than 60 s Not
Video 8 20
83841 523-1 8/20/2006 9:37:33 AM 6.97000029031187 Include Welcome
Video.health video Welcome

[Ken Sheridan]

A row is returned in a query's result set where a row one side of a join
matches a row the other side on the join column(s), so if there is more than
one matching row on either side of a join multiple rows will be returned. If
the columns in these rows where the values differ are not included in the
SELECT clause, however, the rows returned will be identical. This is not
uncommon, and SQL caters for it by means of the SELECT DISTINCT statement, so
all you should need to do to get rid of the duplicates is insert DISTINCT
after the SELECT at the start of the query.

[cterner]

Thanks Ken!

Everything seems to be working.