time difference in two records that is 1min and under 1

[Goha]

How would i get the time difference for only the records that load and unload in i min and under.... here is what i am using.


select car_no, viewlog_date_time, text_message
from viewlog
where car_no <= '500' and (text_message like 'LOADED' or text_message like 'Unload' )
order by car_no, viewlog_date_time


Thanks in advance

 

[DaveStL]

What values are stored in each of those fields? I'm guessing that:

car_no identifies a specific car.
viewlog_date_time contains either a duration or a time stamp.
text_message identifies this as a load or unload process.

The question has to do with the viewlog_date_time field. If this is a duration, simply add &quot;and viewlog_date_time < 60&quot; to the where clause. If the viewlog_date_time field is a time stamp, and it identifies the beginning of the load/unload process, you need an additional field that will identify the ending of the load/unload process (let's call it &quot;ending_date_time). Then just add &quot;and ending_date_time - viewlog_date_time < 60&quot; to the where clause.

 

[Goha]

viewlog_date_time is a timestamp i am looking to see if the next record's viewlog_date_time is 1 minute or less where the following condition holds

1. same car number
2. must be (unload then loaded) or (loaded then unload) not two of the same
3. datetime must be within an interval of one minute or less
of each other (the next record in the table)

Thanks

 

[SQLSister]

Cannot do datetime caluclations on a timestamp field, see following quote from Book OnLine:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.

In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.

 

[DaveStL]

Thanks for setting me straight, SQLSister. I knew that, I guess I was just being sloppy with my terminology.

Goha, the next question is: what is the datatype for the viewlog_date_time field. If it is indeed timestamp, you will not be able to calculate on it, as SQLSister indicated. If it is datetime or smalldatetime, we can continue to look at this.

 

[Goha]

the datatype for all fields are as follows....

car_no char(10)
viewlog_date_time datetime
text_message char(250)

 

[rt63]

select car_no, viewlog_date_time, text_message
from viewlog a
where car_no <= '500' and (text_message like 'LOADED' or text_message like 'Unload' )
and dateadd(mi,1,viewlog_date_time) >
isnull((select min(viewlog_date_time) from viewlog b
where b.car_no = a.car_no
and b.viewlog_date_time > a.viewlog_date_time
and ((b.text_message like 'LOADED'
and a.text_message like 'Unload' )
or (a.text_message like 'LOADED'
and b.text_message like 'Unload' ))) ,dateadd(mi,1,viewlog_date_time))

order by car_no, viewlog_date_time



RT