Set-based approach to finding consecutive records

I’m sure I’ve written about this before, but it comes up so regularly in the newsgroups that I thought I’d write an answer here.

A table (probably an audit table) exists with a field that stores a date signifying when a change was made. You need to return a result-set which has a row for each period of time between modifications.

For example:

CREATE TABLE QtyAdj ( …. , ModDate DATETIME, Qty INT )

–where I haven’t listed all the columns – there should obviously be a primary key on this. In fact, I’ll assume there is a field called ID which is unique.

What we want is:

with modnums as (select *, row_number() over (order by moddate, id) as rn from
qtyadj)
select m_this.moddate, m_next.moddate, m_this.qty
from
 
modnums m_this
  left join
  modnums m_next
    on m_next.rn =
m_this.rn + 1

Which uses row_number() within a table expression to give a unique number to each row ordered by the date. You can then join between two copies of the table expression (love CTEs for that), to get your consecutive records in a single row of your result-set.