Calculating actuals from YTD

[LeeBrown]

How can you calculate actual values using year-to-date values in SQL (using no bespoke function calls).

The source table:

Period
Site
Actual YTD

The Destination table:

Period
Site
Actual YTD
Actual

The YTD values start at period 1 and end at period 12. They start fresh each year. Once complete you should be able to perform a running sum on the Actual column and match the Actual YTD figure.

Example data:
Period 1 YTD Actual = 10
Period 2 YTD Actual = 25
Period 3 YTD Actual = 40

after calculating the actuals:
Period 1 Actual = 10
Period 2 Actual = 15
Period 3 Actual = 15

Any ideas?

 

[r937]

this query assumes that the maximum YTD of any of the earlier periods is in the immediately prior period

obviously this approach is flawed if the Actual is ever negative in any period

[tt]select t1.site
, t1.period
, t1.YTDActual
, case when max(t2.YTDActual) is null
then t1.YTDActual
else t1.YTDActual
- max(t2.YTDActual)
end as Actual
from YTDActuals t1
left outer
join YTDActuals t2
on t1.site = t2.site
and t1.period > t2.period
group
by t1.site
, t1.period
, t1.YTDActual
order
by t1.site
, t1.period[/tt]

tested and works on your sample data

rudy

http://r937.com/

SQL Consulting