dwain.c (1/20/2013)

---

Mark - I'm not sure why you used two window functions:

WITH SourceRecordset AS (

SELECT [index], workcode, value

FROM (

VALUES(1, 'CL', 5),

(2, 'CL', 10),

(3, 'CL', 10),

(4, 'LI', 10),

(5, 'LI', 10),

(6, 'ME', 5),

(7, 'ME', 5),

(8, 'CL', 10),

(9, 'CL', 10),

(10, 'CL', 5),

(11, 'ME', 10),

(12, 'ME', 10)

) d([index], workcode, value)

)

SELECT [index]=MIN([index]), workcode=MAX(workcode), value=SUM(value)

FROM (

SELECT [index], workcode, value

,rn=[index]-ROW_NUMBER() OVER (PARTITION BY workcode ORDER BY [index])

FROM SourceRecordset) a

GROUP BY rn

ORDER BY [index]