Substracting two adjacent rows

Hi!
I have a very large table, containing some DateTime data.
Is it possible to run a query that will return a resultset, containing the
difference between my DataTime values in two adjacent rows?
Example:

ID OnOff TID DateTime
-------------------------------------
1 On 1 11:30:30
2 On 2 11:30:35
3 Off 1 11:35:30
4 Off 2 11:45:35

I want to get a recordset, containing the between Off time and On time for
particular TID, like this:
TID Duration
---------------------------
1 0:05:00 (11:35:30-11:30:30)
2 0:15:00 (11:45:35-11:30:35)

Can this be done with SQL query?
I can do this iterating in a loop, but it seems to slow and clumsy...

I 'd appreciate any help.

Thanks,
Yakimo

"Yakimo" wrote
I have a very large table, containing some DateTime data.
Is it possible to run a query that will return a resultset, containing the
difference between my DataTime values in two adjacent rows?
Example:

ID OnOff TID DateTime
-------------------------------------
1 On 1 11:30:30
2 On 2 11:30:35
3 Off 1 11:35:30
4 Off 2 11:45:35

I want to get a recordset, containing the between Off time and On time for
particular TID, like this:
TID Duration
---------------------------
1 0:05:00 (11:35:30-11:30:30)
2 0:15:00 (11:45:35-11:30:35)

Hi Yakimo,

The general method for "adjacent rows" is to
bring the table into your query twice (joining
in this case on TID).

Alias one table as "t1" and other as "t2"

Set criteria for t1.OnOff = "On"
Set criteria for t2.OnOff = "Off"

"DateTime" is a reserved word. If possible,
change the name of the field to something
else (say "OnOffTime"). In following, I am
going to use "OnOffTime" in its place, but if
you are not able to change it, at least be sure
to put brackets around the fieldname in your
queries. It really is a "gotcha" waiting to happen.

Anyway...in this general method, include "OnOffTime"
from both tables in your query, plus subtraction
(t2.OnOffTime - t1.OnOffTime).

Then use a correlated subquery to set criteria
for t1.OnOffTime.

If I am looking at a specific Off time, what On time
do I want to use for subtraction?

-- same TID
-- OnOff = "On"
-- very latest (Max) On time less than Off time
(if there can be many entries for a TID, then
you may be able to use ID instead:
"latest" (Max) On time ID less than this
Off time ID)

SELECT
t2.TID,
t1.OnOffTime As OnTime,
t2.OnOffTime As OffTime,
(t2.OnOffTime - t1.OnOffTime) As Difference
FROM
yourtable AS t1
INNER JOIN
yourtable AS t2
ON t1.TID = t2.TID
WHERE
t1.OnOff = "ON"
AND
t2.OnOff = "OFF"
AND
t1.OnOffTime =
(SELECT MAX(t3.OnOffTime)
FROM yourtable AS t3
WHERE t3.TID = t1.TID
AND
t3.OnOff = "ON"
AND
t3.OnOffTime t2.OnOffTime);

or

SELECT
t2.TID,
t1.OnOffTime As OnTime,
t2.OnOffTime As OffTime,
(t2.OnOffTime - t1.OnOffTime) As Difference
FROM
yourtable AS t1
INNER JOIN
yourtable AS t2
ON t1.TID = t2.TID
WHERE
t1.OnOff = "ON"
AND
t2.OnOff = "OFF"
AND
t1.ID =
(SELECT MAX(t3.ID)
FROM yourtable AS t3
WHERE t3.TID = t1.TID
AND
t3.OnOff = "ON"
AND
t3.ID t2.ID);

Without testing I may have easily gotten something
wrong above, but hopefully you will be able to adapt
the general method.

Good luck,

Gary Walter

Thanks Gary
That's exactly what I need

../yakimo

"Gary Walter" wrote in message
...

"Yakimo" wrote
I have a very large table, containing some DateTime data.
Is it possible to run a query that will return a resultset, containing
the
difference between my DataTime values in two adjacent rows?
Example:

ID OnOff TID DateTime
-------------------------------------
1 On 1 11:30:30
2 On 2 11:30:35
3 Off 1 11:35:30
4 Off 2 11:45:35

I want to get a recordset, containing the between Off time and On time
for
particular TID, like this:
TID Duration
---------------------------
1 0:05:00 (11:35:30-11:30:30)
2 0:15:00 (11:45:35-11:30:35)

Hi Yakimo,

The general method for "adjacent rows" is to
bring the table into your query twice (joining
in this case on TID).

Alias one table as "t1" and other as "t2"

Set criteria for t1.OnOff = "On"
Set criteria for t2.OnOff = "Off"

"DateTime" is a reserved word. If possible,
change the name of the field to something
else (say "OnOffTime"). In following, I am
going to use "OnOffTime" in its place, but if
you are not able to change it, at least be sure
to put brackets around the fieldname in your
queries. It really is a "gotcha" waiting to happen.

Anyway...in this general method, include "OnOffTime"
from both tables in your query, plus subtraction
(t2.OnOffTime - t1.OnOffTime).

Then use a correlated subquery to set criteria
for t1.OnOffTime.

If I am looking at a specific Off time, what On time
do I want to use for subtraction?

-- same TID
-- OnOff = "On"
-- very latest (Max) On time less than Off time
(if there can be many entries for a TID, then
you may be able to use ID instead:
"latest" (Max) On time ID less than this
Off time ID)

SELECT
t2.TID,
t1.OnOffTime As OnTime,
t2.OnOffTime As OffTime,
(t2.OnOffTime - t1.OnOffTime) As Difference
FROM
yourtable AS t1
INNER JOIN
yourtable AS t2
ON t1.TID = t2.TID
WHERE
t1.OnOff = "ON"
AND
t2.OnOff = "OFF"
AND
t1.OnOffTime =
(SELECT MAX(t3.OnOffTime)
FROM yourtable AS t3
WHERE t3.TID = t1.TID
AND
t3.OnOff = "ON"
AND
t3.OnOffTime t2.OnOffTime);

or

SELECT
t2.TID,
t1.OnOffTime As OnTime,
t2.OnOffTime As OffTime,
(t2.OnOffTime - t1.OnOffTime) As Difference
FROM
yourtable AS t1
INNER JOIN
yourtable AS t2
ON t1.TID = t2.TID
WHERE
t1.OnOff = "ON"
AND
t2.OnOff = "OFF"
AND
t1.ID =
(SELECT MAX(t3.ID)
FROM yourtable AS t3
WHERE t3.TID = t1.TID
AND
t3.OnOff = "ON"
AND
t3.ID t2.ID);

Without testing I may have easily gotten something
wrong above, but hopefully you will be able to adapt
the general method.

Good luck,

Gary Walter