sql server 2008 r2 - SQL - check if the dates are in correct sequence -

i have table this:

id    start      end 1     1-1-2015   29-1-2015 2     30-1-2015  28-2-2015 3     1-3-2015   30-3-2015 ....  

now need sql query check if date missed between end date of row , start date of next row.

e.g. if end date 29-1-2015 start date of next row should next date of i.e. 30-1-2015. if date missed should return error.

you can use apply next record (assuming defining next using order of id), filter out start of next record not match end of current record:

select  *    yourtable t1         cross apply         (   select  top 1 t2.[start]                yourtable t2               t2.id > t1.id             order t2.id         ) nextstart   nextstart.[start] != dateadd(day, 1, t1.[end]); 

if id column has no gaps, use join:

select  *    yourtable t1         inner join yourtable nextstart             on t2.id = t.id + 1   nextstart.[start] != dateadd(day, 1, t.[end]); 

---

if start of 1 period should day after end of previous, why not store single value this. e.g. need store is:

id    start      1     2015-01-01   2     2015-01-30  3     2015-03-01  

now, can define end date, looking next start date:

select  t.id,         t.start,         [end] = dateadd(day, -1, e.start)    dbo.yourtable t         outer apply         (   select  top 1 start                dbo.yourtable t2               t2.id > t.id             order id         ) e; 

you can make view if need both start , end regularly, , ensures not have missing periods.