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]
Sure, but that requires [index] to be contiguous- it wasn't clear from OP whether than was the case.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537