MS SQL

MS SQL RSS

statement

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2009

Posts: 5

Reputation: carsein is an unknown quantity at this point

Solved Threads: 0

carsein

Offline

Newbie Poster

statement

Aug 3rd, 2009

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.

Last edited by carsein; Aug 3rd, 2009 at 5:16 am.

•

Join Date: Dec 2007

Posts: 97

Reputation: VIeditorlover is an unknown quantity at this point

Solved Threads: 5

VIeditorlover VIeditorlover is offline

Offline

Junior Poster in Training

Re: statement

Aug 3rd, 2009

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.

•

Join Date: Aug 2009

Posts: 5

Reputation: carsein is an unknown quantity at this point

Solved Threads: 0

carsein

Offline

Newbie Poster

Re: statement

Aug 3rd, 2009

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

•

Join Date: Dec 2007

Posts: 97

Reputation: VIeditorlover is an unknown quantity at this point

Solved Threads: 5

VIeditorlover VIeditorlover is offline

Offline

Junior Poster in Training

Re: statement

Aug 3rd, 2009

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

•

Join Date: Aug 2009

Posts: 5

Reputation: carsein is an unknown quantity at this point

Solved Threads: 0

carsein

Offline

Newbie Poster

Re: statement

Aug 3rd, 2009

yes..

•

Join Date: Feb 2009

Posts: 3,205

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: statement

Aug 3rd, 2009

Try this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
--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
)
--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
)