Finding discontinuities from a SQL table

sql, sql-server, sql-server-2008, t-sql

Solution

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

Problem

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.

Original source

Related problems