A common solution for calculating accumulated totals prior to SQL Server 2012 was to use a cursor and add each row during iteration.
SQL SERVER 2012 introduced new resources which can provide a more elegant solution to this requirement. The command ROWS BETWEEN UNBOUNDED PRECEDING is the command that works with the ROWS BETWEEN subclause, setting the starting boundary and adding the CURRENT ROW element to indicate the ending boundary for the row to be calculated. Finally the command can be used for graphics such as accumulated earnings in a year. 

Both approaches will be illustrated and compared.  You can then see for yourself how ROWS BETWEEN UNBOUNDED PRECEDING is a better technique - easier and elegant.

The following example below uses the Cursor-TSQL to calculate a running total.

 

declare @year int
declare @month int
declare @task varchar(255)
declare @value decimal (18,2)
declare @total decimal(18,2)= 0
  
--I am using this resource only to illustrate.
  
create table #table
(
  task varchar(255),
  [value] decimal(18,2),
  [total] decimal (18,2),
  [month] int,
  [year] int
)
  
  
declare cursor_values cursor for
  
select
  task.TaskName,
  sum(TaskActualCost) as TaskActualCost,
  month(TaskFinishDate) as [Month],
  Year(TaskFinishDate) as [Year]
from
  MSP_EpmTask task
where
  TaskOutlineLevel =0 and Year(TaskFinishDate)=2011 and TaskActualCost>0
group by
  task.TaskName,
  month(TaskFinishDate),
  Year(TaskFinishDate)
  
open  cursor_values
fetch next from cursor_values
into @task,@value,@month,@year
  
While @@FETCH_STATUS =0
begin
  set @total=@total+@value
        
  insert into #table ([task],[value],[total],[month],[year]) values (@task,@value,@total,@month,@year)
  
  fetch next from cursor_values
  into @task,@value,@month,@year
  
end
  
CLOSE cursor_values;
DEALLOCATE cursor_values;
  
Select * from #table
  
drop table #table

Results 

The same result above might be done through fewer lines.  The following example uses an aggregate window function to calculate a running total. This illustrates the use of these elements:

select
  task.TaskName,
  TaskActualCost as valor,
  sum(TaskActualCost)
            over(
                partition by year(TaskFinishDate) order by month(TaskFinishDate),task.TaskName
                rows between unbounded preceding and current row
            )as Total,
    
  month(TaskFinishDate) as [Month],
  Year(TaskFinishDate) as [Year]
from
  MSP_EpmTask task
where
  TaskOutlineLevel =0 and Year(TaskFinishDate)=2011 and TaskActualCost>0

Benefits

You get the same results as the first example with 14 rather than 41 lines of script whilst also using far less server memory. This is very clearly a better solution. 

The following code show the accumulative of year by month.

select
  max(total) as Total,
  [MONTH],
  [YEAR] from
(
 
select
  task.TaskName,
  TaskActualCost as valor,
  sum(TaskActualCost)
            over(
                partition by year(TaskFinishDate) order by month(TaskFinishDate),task.TaskName
                rows between unbounded preceding and current row
            )as Total,
    
  month(TaskFinishDate) as [Month],
  Year(TaskFinishDate) as [Year]
from
  MSP_EpmTask task
where
  TaskOutlineLevel =0 and Year(TaskFinishDate)=2011 and TaskActualCost>0
 
) as query
 
group by
  [Month],[Year]

Results

Refer?ncias:

DECLARE CURSOR (Transact-SQL)

OVER CLAUSE (Transact-SQL)