|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > In SQL 7.0, I Need to subtract sequential adjacent rows on a field with time!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
In SQL 7.0, I Need to subtract sequential adjacent rows on a field with time!
|
|
| Andrew Ramirez 2002-08-14, 10:23 am |
| I have a SQL table which is written to everytime a verifier scans a
package his initials and the time, date , and number of boxes scanned
are written to a table. This table follows the following format :
Verifier TIME BoxCount DATE
-------- ---- -------- ----
MM 9:46:45 AM 1 8/12/2002
MM 9:47:38 AM 1 8/12/2002
MM 9:48:19 AM 1 8/12/2002
MM 9:49:03 AM 1 8/12/2002
MM 9:49:48 AM 1 8/12/2002
MM 9:50:59 AM 1 8/12/2002
MM 9:53:01 AM 1 8/12/2002
MM 10:03:35 AM 1 8/12/2002
...continues for 36,000+ rows
I need to take each adjacent row and find the difference in time to
see how much time was spent from the time one box was scanned to the
other being scanned and find all those with a difference of 3 minutes
or more, and then if >= 3 minutes I would need to write that value to
a table w/ the verifier name, for example....
e.g. [Reading the above table from top to bottom]
For MM there is a difference of 00:00:53 seconds between 9:46:45 AM &
9:47:38 AM so I would not write this to a table.
Next For MM there is a difference of 00:00:41 seconds between 9:47:38
AM & 9:48:19 so I would not write this to a table.
Next For MM there is a difference of 00:00:44 seconds between 9:48:19
& 9:49:03 AM so I would not write this to a table.
Jumping to the end of this example.....
Lastly, For MM there is a difference of 00:10:34 seconds between
9:53:01 AM & 10:03:35 AM so I would write MM to a table and 00:10:34
to a table.
I would think that a cursor would be the right approach for this row
by row type of thing, but I have no clue on how to go about it. I am
an intermediate user and do need a thorough explanation of how I can
do this, or even just a general example.
Please Reply!!!
| |
| Roy Harvey 2002-08-14, 12:23 pm |
| Andrew,
This should be worth tryig out. It assumes that the Time columns is
of datetime datatype. Performance will depend a lot on how many rows
there are for each Verifier, and how the table is indexed.
SELECT S.Verifier, S.Time,
max(P.Time) as Prior,
datediff(ss,max(P.time),S.Time) as Elapsed
FROM Scans as S
JOIN Scans as P
ON S.Verifier = P.Verifier
AND S.Date = P.Date
AND P.Time < S.Time
GROUP BY S.Verifier, S.Time
HAVING datediff(ss,max(P.time),S.Time) >= 180
Alternately, we could change the join to test:
AND P.Time <= DATEADD(mi,-3,S.Time)
and eliminate the HAVING clause.
Roy
shottarum@yahoo.com (Andrew Ramirez) wrote:
>I have a SQL table which is written to everytime a verifier scans a
>package his initials and the time, date , and number of boxes scanned
>are written to a table. This table follows the following format :
>
>Verifier TIME BoxCount DATE
>-------- ---- -------- ----
>MM 9:46:45 AM 1 8/12/2002
>MM 9:47:38 AM 1 8/12/2002
>MM 9:48:19 AM 1 8/12/2002
>MM 9:49:03 AM 1 8/12/2002
>MM 9:49:48 AM 1 8/12/2002
>MM 9:50:59 AM 1 8/12/2002
>MM 9:53:01 AM 1 8/12/2002
>MM 10:03:35 AM 1 8/12/2002
>
>..continues for 36,000+ rows
>
>I need to take each adjacent row and find the difference in time to
>see how much time was spent from the time one box was scanned to the
>other being scanned and find all those with a difference of 3 minutes
>or more, and then if >= 3 minutes I would need to write that value to
>a table w/ the verifier name, for example....
>
>e.g. [Reading the above table from top to bottom]
>
>For MM there is a difference of 00:00:53 seconds between 9:46:45 AM &
>9:47:38 AM so I would not write this to a table.
>
>Next For MM there is a difference of 00:00:41 seconds between 9:47:38
>AM & 9:48:19 so I would not write this to a table.
>
>Next For MM there is a difference of 00:00:44 seconds between 9:48:19
>& 9:49:03 AM so I would not write this to a table.
>
>Jumping to the end of this example.....
>
>Lastly, For MM there is a difference of 00:10:34 seconds between
>9:53:01 AM & 10:03:35 AM so I would write MM to a table and 00:10:34
>to a table.
>
>I would think that a cursor would be the right approach for this row
>by row type of thing, but I have no clue on how to go about it. I am
>an intermediate user and do need a thorough explanation of how I can
>do this, or even just a general example.
>
>Please Reply!!!
|
|
|
|
|