October 24, 2001 at 4:37 pm
CREATE TABLE [dbo].[log] (
[message] [nchar] (30) NOT NULL ,
[datetime] [datetime] NOT NULL ,
[details] [ntext] ,
[timestamp_column] [timestamp] NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
)
How construct a select that gives to each row the difference between the [datetime] in current row and the next closest row (in time)?
With select, without cursor.
October 24, 2001 at 6:23 pm
Why would you need to do that?
Andy
October 24, 2001 at 6:40 pm
Hi attilas,
I am curious about this one. It seems that you may be like many of us have been at one time and that is under the assumption that because the datatype is 'timestamp' that the value would represent a time value. In reality it has nothing to do with date or time. I found an excellent description in the Osbornes series books SQL 2000 Stored Procedure Programming.
Jody
October 29, 2001 at 5:01 pm
I suspect you want something like this - probably are easier ways and ma=ybe you could use the ID if the datetimes are consecutive.
select log.ID, Log.[datetime] ,
case when coalesce(l2.[datetime],'1 jan 2500') - log.[datetime] < log.[datetime] - coalesce(l1.[datetime],'1 jan 1900') then l2.[datetime] - log.[datetime] else log.[datetime] - l1.[datetime] end
left join log l1 on log.[datetime] > l1.[datetime]
left join log l2 on log.[datetime] < l2.[datetime]
group by log.ID, Log.[datetime]
Cursors never.
DTS - only when needed and never to control.
December 8, 2002 at 6:28 pm
select one.CrDate, two.crDate, datediff(s, one.crdate, two.crdate)
from table1 one
join table1 two
on two.crDate =
(
select min(crDate)
from table1 inside
where inside.crdate > one.crDate
)
here's a simpler self-join
Note indexes on CRDATE will make this zoom.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply