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.