mh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude faster

I adapted your query to fit some different test data

[font="Courier New"]

drop table sales

CREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)

CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)

go

DECLARE @DayCount smallint, @Sales money, @channel varchar(50)

SET @DayCount = 0

SET @Sales = 10

set @channel = 'a'

WHILE @DayCount < 10000

BEGIN

INSERT Sales VALUES (@DayCount,@channel, @Sales)

SET @DayCount = @DayCount + 1

SET @Sales = @Sales + 15

set @channel = case

when right(cast(@daycount as varchar), 1) = 0 then 'a'

when right(cast(@daycount as varchar), 1) = 1 then 'b'

when right(cast(@daycount as varchar), 1) = 2 then 'c'

when right(cast(@daycount as varchar), 1) = 3 then 'd'

when right(cast(@daycount as varchar), 1) = 4 then 'e'

when right(cast(@daycount as varchar), 1) = 5 then 'f'

when right(cast(@daycount as varchar), 1) = 6 then 'g'

when right(cast(@daycount as varchar), 1) = 7 then 'h'

when right(cast(@daycount as varchar), 1) = 8 then 'i'

when right(cast(@daycount as varchar), 1) = 9 then 'j'

end

END

select * from sales

with cte as

(

select *, Row_Number() OVER(PARTITION BY channel ORDER BY channel) as RowNum

from sales

)

select a.rownum, a.daycount,a.channel, a.sales,

SUM(b.sales ) - a.sales as Bal

from cte a

LEFT outer join cte b

on a.channel = b.channel and a.RowNum > B.RowNum

group by a.rownum, a.channel, a.daycount, a.sales

order by a.channel, a.daycount

CREATE TABLE #Sales (DayCount smallint, Channel varchar(50), Sales money, RunningTotal money)

DECLARE @RunningTotal money

declare @old_channel varchar(50)

SET @RunningTotal = 0

set @old_channel = ''

DECLARE rt_cursor CURSOR

FOR

SELECT DayCount, Channel, Sales

FROM Sales

order by channel, daycount

DECLARE @DayCount smallint, @Sales money, @channel varchar(50)

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Channel, @Sales

WHILE @@FETCH_STATUS = 0

BEGIN

SET @RunningTotal = @RunningTotal + @Sales

INSERT #Sales VALUES (@DayCount,@channel, @Sales, @RunningTotal)

set @old_channel = @channel

FETCH NEXT FROM rt_cursor INTO @DayCount,@channel, @Sales

if @old_channel <> @channel set @runningtotal = 0

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT * FROM #Sales ORDER BY channel,DayCount

DROP TABLE #Sales[/font]