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]