Posted by MGFoster on 12/28/05 03:24

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't say what time part (days, months, years, hours, minutes,
seconds) you want returned. My example will show hours.

SELECT NodeID, LineStation, MessageFlag, UTC_Time,
DateDiff(Hour, (SELECT MAX(UTC_Time) FROM table_name
WHERE NodeID=T.NodeID AND LineStation=T.LineStation
AND UTC_Time < T.UTC_Time),
UTC_Time) As HoursInterval

FROM table_name As T
WHERE .... < your criteria > ...
ORDER BY NoteID, LineStation, UTC_Time

The "table_name" in both the main query & the subquery should be the
same.

Your data implies that the LineStation is receiving a message
(MessageFlag) at specified times (UTC_TIME). Therefore, I set up the
query to return info on LineStations on the same NodeID. If you just
want to track messages on the NodeID, no matter the LineStation, then
remove the "AND LineStation=T.LineStation" part of the subquery's
criteria (WHERE clause), and remove the LineStation from the ORDER BY
clause.

See the SQL Server BooksOnLine for more info on DateDiff() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HpM4echKqOuFEgEQJWFgCdEMfPaY7aQPYOL66ZJryJpR4fTMMAoO0r
WEDMaPHxQiZ352eHx0ER72Ur
=YKsf
-----END PGP SIGNATURE-----

iamonthisboat@gmail.com wrote:
>
>
>
>
> I have a data set like so:
>
>
> UTC_TIME Timestamp NodeID Message Flag
> Line
> Station
> 11/19/2005 10:45:07 1132397107.91 1 3 5 1028
> 1034
> 11/3/2005 21:05:35 1131051935.20 2 3 5 1009
> 1043
> 11/25/2005 21:12:16 1132953136.59 3 3 5 1037
> 1049
>
>
> I added the UTC_TIME column in as aconversion of the unix timestamp in
>
> the TIMESTAMP column.
>
>
> Keeping things simple and straightforward, I need to be able to
> calculate the difference from one record to the next (ordered by
> TIMESTAMP or UTC_TIME) and output the result into another column in the
>
> table.
>
>
> NODEID is the unique id.
>
>
> First, what is the function to do so if, say, I only wanted to
> calculate the difference between 2 records as just a basic SELECT
> statement. That way I can answer quick question based on any one or two
>
> NODEID's.
>
>
> Second, how would I further that to continually calculate (as stated
> above)?
>
>
> WOuld this be a stored procedure? A trigger? A cursor?
>
>
> I am learning as I go here. Any help is greatly appreciated.
>
>
> R.
>

[Back to original message]