One of the more obscure requirements that a developer may find themselves facing is the need to compare a row with its immediate sibling. One such case is when a list of values needs to be processed to produce a moving average or to smooth a sequence of statistical numbers where their order is important.  For example, values lying along a time line. The solution is actually quite simple, but not immediately obvious.

SELECT	AVG(e.Gap)
FROM	(
		SELECT		d.Period,
				MAX(d.Stat) - MIN(d.Stat) AS Gap
		FROM		(
					SELECT	Stat,
						Period,
						0 AS aFake
					FROM	tbStats

					UNION ALL

					SELECT	Stat,
						DATEADD(DAY, 1, Period),
						1
					FROM	tbStats
				) AS d
		GROUP BY	d.Period
		HAVING		COUNT(*) = 2
	) AS e

select
    x.r_id xId,
    y.r_id yId, 
    x.stat xStat,
    y.stat yStat 
from 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from tbStats ) x 
    left join 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from tbStats ) y 
    on x.r_id + 1 = y.r_id 
order by 
    x.r_id

quote:

---

Originally posted by mharr

I believe you can still use solution #1 even if the rows are out of order, by using ROW_NUMBER() function to create a new perfectly sequential ID: ....

---

select
    x.r_id xId,
    y.r_id yId, 
    x.stat xStat,
    y.stat yStat,
from 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) + 1 as r_id,
     stat from #tbStats ) x 
    left join 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from #tbStats ) y 
    on x.r_id = y.r_id 
order by 
    x.r_id

select
    avg(abs(x.stat - y.stat)) movingAvg  
from 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) + 1 as r_id,
     stat from #tbStats ) x 
    left join 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from #tbStats ) y 
    on x.r_id = y.r_id 

Paul Alcon 2	  32 reads	 15 duration
Paul Alcon 3	  26 reads	  1 duration
Peso		   6 reads	  1 duration
mharr		  20 reads	  2 duration

Paul Alcon 2	6161 reads	140 duration
Paul Alcon 3	6155 reads	 42 duration
Peso		  16 reads	 15 duration
mharr		  16 reads	 19 duration

Peso		  2544 reads	 810 duration
mharr		  2544 reads	1498 duration

quote:

---

Originally posted by mharr

I believe you can still use solution #1 even if the rows are out of order, by using ROW_NUMBER() function to create a new perfectly sequential ID:

select
    x.r_id xId,
    y.r_id yId, 
    x.stat xStat,
    y.stat yStat 
from 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from tbStats ) x 
    left join 
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from tbStats ) y 
    on x.r_id + 1 = y.r_id 
order by 
    x.r_id

---

with tmpStats AS
    (SELECT ROW_NUMBER() OVER(ORDER BY period ASC, id ASC) as r_id,
     stat from tbStats )
select
    x.r_id xId,
    y.r_id yId, 
    x.stat xStat,
    y.stat yStat 
from 
    tmpStats x 
    left join 
    tmpStats y 
    on x.r_id + 1 = y.r_id 
order by 
    x.r_id

/*
100 records generated

Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#t'. Scan count 3, logical reads 9,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 29 ms.

Table '#t'. Scan count 30, logical reads 18,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 46 ms,  elapsed time = 35 ms.
-------------------------------------------
500 records generated

Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#t'. Scan count 3, logical reads 39,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 203 ms.

Table '#t'. Scan count 30, logical reads 78,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 205 ms,  elapsed time = 157 ms.
---------------------------------------------
1000 records generated

Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#t'. Scan count 3, logical reads 78,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 716 ms.

Table '#t'. Scan count 28, logical reads 312,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table 'Worktable'. Scan count 8, logical reads 775568,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 152624 ms,  elapsed time = 40658 ms.
--------------------------------------------------
*/
set statistics io off
set statistics time off
set nocount on
-----------------------------------
-- create some data
-- ----------------
-- This actually models part of a document workflow.
-- The flow has been simplified to just 3 states.
-- Documents start in state 'F' and may move to
-- either 'C' (compete) or 'P' (queried) or may be
-- amended and stay in state 'F'. Once in state 'P',
-- the query may be answered, so the document goes 
-- to state 'F' or it may be amended and stay in
-- state 'P'. The probabilities of moving between
-- states are arbitrarily set for the model. In
-- order to simulate repeated queries making it
-- more likely for the document to be completed,
-- the probability is increased on each step.
-- Time intervals are randomly assigned between
-- 5 minutes and 1 month. In the model, the generated
-- IDs are sequential. In the real application, they
-- may not be.
-- 
drop table #t
create table #t (id int, st char(1), date datetime)

declare @n int
declare @st char(1)
declare @date datetime
declare @Cp float
declare @p float

set @n = 1000 -- <<<<<<<<<<<<<<<<< How many ID chains to generate
print convert(varchar(5),@n)+' records generated'
while @n > 0
begin
	set @st = 'F'
	set @date = dateadd(mi,rand()*1440.0*28,convert(datetime,'2008/2/1'))
	insert #t (id,st,date) values(@n,@st,@date)
	set @cp = 0.3
	while @st <> 'C'
	begin
		if @st = 'F'
		begin
			set @p = rand()
			if @p < @cp
				set @st = 'C'
			else
				if @p < 0.99
					set @st = 'P'
		end
		else
		begin
			set @p = rand(1)
			if @p < 0.9
				set @st = 'F'
		end
		set @date = dateadd(mi,rand()*1440*3+5,@date)
		insert #t (id,st,date) values(@n,@st,@date)
		set @cp = @cp * 1.1
	end
	set @n = @n -1
end
select * from #t

set nocount off
set statistics io on
set statistics time on
--------------------------------------------
-- version 1 - triangular join
------------------------------
-- This is the method currently used and
-- produces the desired result
--
select 
	id,
	sta,
	min(da) as 'da',
	stb,
	db 
from (
	select
		a.id,
		a.sta,
		a.da,
		b.st as 'stb',
		b.date as db
	from (
		select
			id,
			sta,
			da,
			min(db) as 'db'
		from (
			select 
				a.id,
				a.st as 'sta',
				a.date as 'da',
				b.st as 'stb',
				min(b.date) as 'db' 
			from
				#t a
			join
				#t b
					on a.id=b.id
					and a.date < b.date
					and a.st <> b.st
			group by
				a.id,
				a.st,
				a.date,
				b.st
		) x
		group by
			id,
			sta,
			da
	) a 
	join
		#t b
			on a.id=b.id
			and a.db=b.date
) x
group by
	id,
	sta,
	stb,
	db
order by
	id,
	min(da)

--------------------------------------------
-- version 2 - using self join by row_number + 1 = row_number
------------
-- this was derived from comments following the article at
-- http://www.sqlteam.com/article/joining-to-the-next-sequential-row
--
;with t1 as (
select
	row_number() over(order by id,date) as 'r_id',
	id,
	st,
	date
from
	#t
)
,t4 as (
select
	row_number() over(order by id,date) as 'r_id',
	id,
	st,
	date 
from (
	select top 1
		id,
		st,
		date
	from
		t1 
	order by
		id,
		date
	union all
	select
		id,
		st,
		date
	from (
		select
			b.id,
			b.st,
			b.date
		from
			t1 a
		join
			t1 b 
				on a.r_id+1=b.r_id
		where 
			(a.id=b.id and a.st<>b.st)
		or	(a.id<>b.id)
	) z
) x
)
select
	a.id,
	a.st as 'sta',
	a.date as 'da',
	b.st as 'stb',
	b.date as 'db'
from
	t4 a
join
	t4 b
		on a.r_id+1=b.r_id
		and a.id=b.id
order by
	id,
	da

set statistics time off
set statistics io off

;WITH Yak (id, st, date, rowid, col)
AS (
	SELECT	id,
		st,
		date,
		ROW_NUMBER() OVER (ORDER BY id, date),
		0
	FROM	#t
)

SELECT		id,
		MAX(CASE WHEN col = 0 THEN st ELSE '' END) AS sta,
		MAX(CASE WHEN col = 0 THEN date ELSE '' END) AS da,
		MAX(CASE WHEN col = 1 THEN st ELSE '' END) AS stb,
		MAX(CASE WHEN col = 1 THEN date ELSE '' END) AS db
FROM		(
			select id, st, date, rowid, col from Yak
			union all
			select id, st, date, rowid - 1, col + 1 from Yak
		) AS d
group by	id,
		rowid
having		count(*) = 2
order by	rowid

derek 1 - triangular join

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t'. Scan count 3, logical reads 78, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 141 ms,  elapsed time = 1518 ms.

derek 2  - cte's

Table '#t'. Scan count 16, logical reads 208, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 702520, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 55094 ms,  elapsed time = 121561 ms.

peso

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t'. Scan count 2, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 565 ms.

-- Peso
;WITH Yak (id, st, date, RowID)
AS (
	SELECT	id,
		st,
		date,
		ROW_NUMBER() OVER (ORDER BY id, date)
	FROM	Derek
)

SELECT		id,
		MAX(CASE WHEN col = 0 THEN st ELSE '' END) AS sta,
		MAX(CASE WHEN col = 0 THEN date ELSE '' END) AS da,
		MAX(CASE WHEN col = 1 THEN st ELSE '' END) AS stb,
		MAX(CASE WHEN col = 1 THEN date ELSE '' END) AS db
FROM		(
			SELECT id, st, date, RowID, 0 AS Col FROM Yak
			UNION ALL
			SELECT id, st, date, RowID - 1, 1 FROM Yak
		) AS d
GROUP BY	id,
		RowID
HAVING		COUNT(*) = 2
ORDER BY	RowID

-- Derek 1
select 
	id,
	sta,
	min(da) as 'da',
	stb,
	db 
from (
	select
		a.id,
		a.sta,
		a.da,
		b.st as 'stb',
		b.date as db
	from (
		select
			id,
			sta,
			da,
			min(db) as 'db'
		from (
			select 
				a.id,
				a.st as 'sta',
				a.date as 'da',
				b.st as 'stb',
				min(b.date) as 'db' 
			from
				Derek a
			join
				Derek b
					on a.id=b.id
					and a.date < b.date
					and a.st <> b.st
			group by
				a.id,
				a.st,
				a.date,
				b.st
		) x
		group by
			id,
			sta,
			da
	) a 
	join
		Derek b
			on a.id=b.id
			and a.db=b.date
) x
group by
	id,
	sta,
	stb,
	db
order by
	id,
	min(da)

-- Derek 3
;with t1 as (
select
	row_number() over(order by id,date) as 'r_id',
	id,
	st,
	date
from
	Derek
)
,t4 as (
select
	row_number() over(order by id,date) as 'r_id',
	id,
	st,
	date 
from (
	select top 1
		id,
		st,
		date
	from
		t1 
	order by
		id,
		date
	union all
	select
		id,
		st,
		date
	from (
		select
			b.id,
			b.st,
			b.date
		from
			t1 a
		join
			t1 b 
				on a.r_id+1=b.r_id
		where 
			(a.id=b.id and a.st<>b.st)
		or	(a.id<>b.id)
		group by b.id, b.st, b.date
	) z
) x
)
select
	a.id,
	a.st as 'sta',
	a.date as 'da',
	b.st as 'stb',
	b.date as 'db'
from
	t4 a
join
	t4 b
		on a.r_id+1=b.r_id
		and a.id=b.id
order by
	id,
	da

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Derek'. Scan count 2, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 790 ms.

SET @n = 100 -- Peso

Table 'Derek'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 345 ms.

SET @n = 10 -- Peso

Table 'Derek'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

quote:

---

Wow. Where did those values come from?

---

id	st	date
19	F	2008-02-11 14:58:00.000
19	P	2008-02-11 21:17:00.000
19	P	2008-02-14 11:20:00.000
19	F	2008-02-15 02:11:00.000
19	P	2008-02-15 06:07:00.000
19	P	2008-02-16 05:27:00.000
19	F	2008-02-18 09:22:00.000
19	C	2008-02-19 05:50:00.000

id	sta	da	stb	db
19	F	2008-02-11 14:58:00.000	P	2008-02-11 21:17:00.000
19	P	2008-02-11 21:17:00.000	F	2008-02-15 02:11:00.000
19	F	2008-02-15 02:11:00.000	P	2008-02-15 06:07:00.000
19	P	2008-02-15 06:07:00.000	F	2008-02-18 09:22:00.000
19	F	2008-02-18 09:22:00.000	C	2008-02-19 05:50:00.000

id	sta	da	stb	db
19	F	2008-02-11 14:58:00.000	P	2008-02-11 21:17:00.000
19	P	2008-02-11 21:17:00.000	P	2008-02-14 11:20:00.000
19	P	2008-02-14 11:20:00.000	F	2008-02-15 02:11:00.000
19	F	2008-02-15 02:11:00.000	P	2008-02-15 06:07:00.000
19	P	2008-02-15 06:07:00.000	P	2008-02-16 05:27:00.000
19	P	2008-02-16 05:27:00.000	F	2008-02-18 09:22:00.000
19	F	2008-02-18 09:22:00.000	C	2008-02-19 05:50:00.000

DECLARE	@Seq INT,
	@Yak INT,
	@st CHAR(1)

SET	@Seq = -1

UPDATE	Derek
SET	@Seq = @Seq + 1,
	@Yak = Seq =	CASE
				WHEN st = @st THEN NULL
				ELSE @Seq
			END,
	@Seq =	CASE
			WHEN @Yak IS NULL THEN @Seq - 1
			ELSE @Seq
		END,
	@st = st

SELECT		id,
		MAX(CASE WHEN Col = 0 THEN st ELSE '' END) AS sta,
		MAX(CASE WHEN Col = 0 THEN date ELSE '' END) AS da,
		MAX(CASE WHEN Col = 1 THEN st ELSE '' END) AS stb,
		MAX(CASE WHEN Col = 1 THEN date ELSE '' END) AS db
FROM		(
			SELECT	id,
				st,
				date,
				Seq,
				0 AS Col
			FROM	Derek
			WHERE	Seq IS NOT NULL

			UNION ALL

			SELECT	id,
				st,
				date,
				Seq - 1,
				1
			FROM	Derek
			WHERE	Seq IS NOT NULL
		) AS d
GROUP BY	id,
		Seq
HAVING		COUNT(*) = 2
ORDER BY	id,
		MAX(CASE WHEN Col = 0 THEN date ELSE '' END)