statement

[carsein]

hi...I've tried to think about this for 2 days but I can't really find the solution. Hope anyone can help. What statement should I write for this:There is a table of Parcel. In the table there are ParcellId and the timestamp. I have to select parcels that have the time between two parcells is only less than 3 seconds. Others less than 3 sec should be ignored.

[VIeditorlover]

Are talking about a form of soft realtime? Please try to explain why is 3s limit so important for you, maybe it would be much easier to help you.

[carsein]

I mean the difference between timestamp is 3 sec(or any given value).

[VIeditorlover]

So you have many records and you want to see only pairs of rows created in less than 3 sec time window, right?

[carsein]

yes..

[sknake]

Try this:

--Create a simulation table
IF OBJECT_ID('ParcelTest', 'U') IS NOT NULL DROP TABLE ParcelTest
Create Table ParcelTest
(
  ID int identity(1000, 1) PRIMARY KEY,
  ShipDate DateTime,
  Name varchar(30)
)
GO
--Create test data
Declare @DateTime DateTime
Set @DateTime = Floor(Cast(GetDate() as float))

Insert Into ParcelTest (ShipDate, Name) Values (DateAdd(second, -40, @DateTime), 'Parcel')
Insert Into ParcelTest (ShipDate, Name) Values (@DateTime, 'Parcel')
Insert Into ParcelTest (ShipDate, Name) Values (DateAdd(second, 1, @DateTime), 'Parcel')
Insert Into ParcelTest (ShipDate, Name) Values (DateAdd(second, 3, @DateTime), 'Parcel')
Insert Into ParcelTest (ShipDate, Name) Values (DateAdd(second, 70, @DateTime), 'Parcel')

GO

--Here is the meat of the question:
Declare @secondsBetween int
Set @secondsBetween = 3

Select *
From ParcelTest
Where EXISTS
(
  Select *
  From ParcelTest As x
  Where x.ID <> ParcelTest.ID and Abs(DateDiff(second, ParcelTest.ShipDate, x.ShipDate)) <= @secondsBetween
)