Transact-SQL: Declare cursor versus Windows With Over - Running totals - Accumulated Earnings
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
https://ricardolacerda.files.wordpress.com/2015/02/resultcursor1.png
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
https://ricardolacerda.files.wordpress.com/2015/02/results2.png
https://ricardolacerda.files.wordpress.com/2015/02/grafico.png
Refer?ncias: