Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 unique event - need of lead\led function sql 2008

Author  Topic 

claudia
Starting Member

1 Post

Posted - 2009-06-09 : 07:53:56
Hi,
I am really stucked and I really do need a hint!!

I am working with time series and I am looking for specific events which took place over an unknown and unrelevant time period.
I am only interested in the count, but if I count the event, I get a lot entries therefore that the event takes place during a period of an hour or even longer... so I do have a lot of entries for each event.

I need a funtion as just count if the difference between the timestemp in the row before is bigger than 30 min.

How do I specify this "in the row before" in sql??

Thanks a lot in advance,
Claudia

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-09 : 08:13:04
you can use ROW_NUMBER() to get a strict sequence and then join it to itself to compare the row before. Here I've used a CTE (though you don't need to)

DECLARE @foo TABLE (
[val] CHAR(1)
, [timestamp] DATETIME
)

INSERT @foo
SELECT 'a', '2009-01-01T11:31:00'
UNION ALL SELECT 'b', '2009-01-01T12:00:00'
UNION ALL SELECT 'c', '2009-03-01T12:00:00'
UNION ALL SELECT 'd', '2009-03-01T17:00:00'
UNION ALL SELECT 'e', '2008-01-01T12:00:00'
UNION ALL SELECT 'f', '2008-01-01T12:20:12'
UNION ALL SELECT 'g', '2008-01-01T12:50:00'

-- Show all rows from @foo
SELECT * FROM @foo

-- Select rows that are within 30 mins of the row before (ordered by val ASC)
;WITH orders AS (
SELECT
ROW_NUMBER() OVER(ORDER BY [val] ASC) AS [pos]
, [val] AS [val]
, [timeStamp] AS [timestamp]
FROM
@foo
)
SELECT
a.*
FROM
orders a

JOIN orders b ON
b.[pos] = a.[pos] + 1
AND DATEDIFF(MINUTE, a.[timeStamp], b.[timeStamp]) BETWEEN 0 AND 31



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
   

- Advertisement -