Get the time between first and last record

[nitai_co]

Hi all,

I have a result table. In the first record I have the start time and in each
record obviously another time then at the last record I have the final time.
All those records have a identical ID.

Now I have to get the time spend between the first and the last record. This
is were I am stuck. Any clues?

Result Table:

ID<tab>value<tab>time
1<tab>A<tab>10:15
1<tab>B<tab>10:20
1<tab>C<tab>10:30

So the time spend here would be for ID "1" 15 minutes.

TIA.

Nitai

[jdeline]

The ID is identical across all records? That's strange.

Anyway, since the recordset is a collection, you can index it as an array. So
your first record would be
myQuery.time[1]
and your last record would be
myQuery.time[myQuery.RecordCount].

[BKBK]

Assume that the entries in the 'time' column are in a datetime
format. If they're not, you can easily convert them. Then you could
try something like

SELECT DATEDIFF(second, MIN(time), MAX(time)) as timeDiff
WHERE ID = 1

This will give the value you are looking for, in seconds.

[jdeline]

BTW, "time" is a reserved word in some systems.

[BKBK]

>... "time" is a reserved word in some systems.
Yes, we must take JDeline's comment into account.

Therefore, assume that the entries in the 'theTime' column are in a datetime
format. If they're not, you can easily convert them. Then you could
try something like

SELECT DATEDIFF(second, MIN(theTime), MAX(theTime)) as timeDiff
WHERE ID = 1

This should give the value you are looking for, in seconds.