InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:32:42 GMT20http://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspx[quote][b]dwain.c (1/20/2013)[/b][hr]Mark - I'm not sure why you used two window functions:[code="sql"]WITH SourceRecordset AS (SELECT [index], workcode, valueFROM ( 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) aGROUP BY rnORDER BY [index][/code][/quote]Sure, but that requires [index] to be contiguous- it wasn't clear from OP whether than was the case.Mon, 21 Jan 2013 01:46:02 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxMark - I'm not sure why you used two window functions:[code="sql"]WITH SourceRecordset AS (SELECT [index], workcode, valueFROM ( 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) aGROUP BY rnORDER BY [index][/code]Sun, 20 Jan 2013 18:05:02 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxYour solution worked perfectly! Thank you!Fri, 18 Jan 2013 09:13:22 GMTgates 23131http://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxThank you for this. I will test it on my system to verify that I am getting the desired results.Fri, 18 Jan 2013 09:07:15 GMTgates 23131http://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxUsing the technique here [url]http://www.sqlservercentral.com/articles/T-SQL/71550/[/url][code="sql"]WITH SourceRecordset AS (SELECT [index], workcode, valueFROM ( 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)),Grouped AS (SELECT [index], workcode, value, ROW_NUMBER() OVER(ORDER BY [index])- ROW_NUMBER() OVER(PARTITION BY workcode ORDER BY [index]) AS rnDiffFROM SourceRecordset)SELECT MIN([index]) AS [index], workcode, SUM(value) AS valueFROM GroupedGROUP BY workcode,rnDiffORDER BY MIN([index]);[/code]Fri, 18 Jan 2013 08:56:13 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxOops, bad code deleted!Fri, 18 Jan 2013 08:52:17 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1408946-3077-1.aspxPlease forgive my ignorance if this is a simple fix but I am having a brain cramp. The goal: derive new records with subtotals of a recordset.Source recordset:index, workcode, value1, CL, 52, CL, 103, CL, 104, LI, 105, LI, 106, ME, 57, ME, 58, CL, 109, CL, 1010, CL, 511, ME, 1012, ME, 10Output:1, CL, 254, LI, 206, ME, 108, CL, 2511, ME, 20Notice that each break of the code is what triggers a new output record. Any assistance on TSQL code for accomplishing this would be greatly appreciated.Thanks in advance!Fri, 18 Jan 2013 08:34:18 GMTgates 23131