Lynn Pettis (9/1/2014)

---

dwain.c (9/1/2014)

---

Not exactly a traditional gaps and islands problem but fun nevertheless. Here's a relatively simple alternative.

DECLARE @system_log TABLE(

PK_ID int PRIMARY KEY

,Sequence_ID int null

)

INSERT @system_log(

PK_ID

,Sequence_ID

)VALUES

(1035590, 35587),

(1035589, NULL),

(1035586, NULL),

(1035585, NULL),

(1035584, NULL),

(1035583, 35583),

(1035582, NULL),

(1035581, NULL),

(1035579, NULL),

(1035578, 35553),

(1035554, NULL),

(1035552, 35551),

(1035550, 35550)

SELECT PK_ID, Sequence_ID=CASE WHEN Sequence_ID IS NOT NULL THEN Sequence_ID ELSE

ROW_NUMBER() OVER (PARTITION BY rn2 ORDER BY PK_ID)-1 END

FROM

(

SELECT PK_ID, Sequence_ID

,rn2=MAX(Sequence_ID) OVER (ORDER BY PK_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FROM @system_log

) a

ORDER by PK_ID desc;

Dwain, I like yours as it appears to be more efficient than mine. I rewrote it slightly to make it cleaner in my opinion.

DECLARE @system_log TABLE(

PK_ID int PRIMARY KEY

,Sequence_ID int null

)

INSERT @system_log(

PK_ID

,Sequence_ID

)VALUES

(1035590, 35587),

(1035589, NULL),

(1035586, NULL),

(1035585, NULL),

(1035584, NULL),

(1035583, 35583),

(1035582, NULL),

(1035581, NULL),

(1035579, NULL),

(1035578, 35553),

(1035554, NULL),

(1035550, 35550);

WITH BaseData as (

SELECT

PK_ID,

Sequence_ID,

rn2 = MAX(Sequence_ID) OVER (ORDER BY PK_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FROM

@system_log

)

SELECT

PK_ID,

Sequence_ID = CASE WHEN Sequence_ID IS NOT NULL

THEN Sequence_ID

ELSE ROW_NUMBER() OVER (PARTITION BY rn2 ORDER BY PK_ID) - 1

END

FROM

BaseData

ORDER BY

PK_ID DESC;