Finding discontinuities from a SQL table

Question

There is probably a quite simple solution to my problem, but I'm having great touble formulating a good search phrase for it. I have a table containing timestamps and counts:

2013-08-15 14:43:58.447    5
2013-08-15 14:44:58.307    12
2013-08-15 14:45:58.383    14
2013-08-15 14:46:58.180    0
2013-08-15 14:47:58.210    4
2013-08-15 14:48:58.287    6
2013-08-15 14:49:58.550    12
2013-08-15 14:50:58.440    2
2013-08-15 14:51:58.390    5

As you can see, the count increases and then gets emptied once in a while. Searching for the rows where count = 0 is easy, but sometimes the count is increased before the zero count has been logged. At 14:49 the count is 12, it is then reset to 0 and incremented to 2 before the next log at 14:50.

I need to list the timestamps where the count is less than the count before:

2013-08-15 14:46:58.180    0
2013-08-15 14:50:58.440    2

I started to make a join on the table itself, to compare two rows but the SQL soon got very messy.

Answer 1

What this does is it creates a row number based on the ts (datetime) column, then it is easier to join to the previous entry. It then compares the times and the counts to find the exceptions.

;with cte as 
(
    select * ,
      ROW_NUMBER() over (order by ts) rn    
    from yourtable
)
    select c1.* from cte c1
        inner join cte c2
            on c1.rn=c2.rn+1
            and c1.c < c2.c

Answer 2

Also in this case you can use LEAD() function:

with CTE as
(
select t.*, LEAD(ct) OVER (ORDER BY dt DESC) as LEAD_CT from t
)  
select dt,ct from CTE where LEAD_CT>CT

SQLFiddle demo

UPD: LEAD() is available from version SQLServer 2012. In 2008 you can replace it with a subquery:

select *
      FROM T as T1
      where (SELECT TOP 1 ct FROM T 
                             WHERE T.dt<T1.DT
                             ORDER BY dt DESC) >CT

SQLFiddle demo

Answer 3

Using ROW_NUMBER() function you can assign numbers on your order and use them for join:

WITH CTE_RN AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY [TimeStamp]) RN
    FROM Table1
)
SELECT r2.* 
FROM CTE_RN r1
    INNER JOIN CTE_RN r2 ON r1.RN +1 = r2.RN
WHERE r1.count > r2.count

SQLFiddle DEMO