How to calculate running totals \ subtotals (like YTD)

If you need to calculate running totals \ subtotals (i.e. year-to-date figures) you can use
a lot of methods (like CTE's). In SQL Server 2008 you can use the GROUP BY ROLLUP function.

However, if you are on 2005 the most efficient way to do this:



is running a query like:

declare
@value float
, @prevorganisationalunitid int
, @prevperiodyear int

update FACT_MEASURE
set @value = valueYTD =
case
when
Year = @PrevPeriodYear
or OrganisationalUnitid = @prevorganisationalunitid
then @value+isnull( value ,0)
else value
end
, @PrevPeriodYear = Year
, @prevOrganisationalUnitid = OrganisationalUnitid


This script only works if the sorting is correct
(order by measure,organisationalunitid,periodid).
To ensure this,
create a primary key on periodid, organisationalunitid and measure.