0

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.

3
Contributors
5
Replies
6
Views
8 Years
Discussion Span
Last Post by sknake
0

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.

0

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

0

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

0

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
)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.