12 months rolling sum

[Pekka]

Hi, I'm trying to make a 12 months rolling total query. I have a table foe example:
Period Year Total
1 2004 123
2 2004 144
3 2004 1244
.
.
1 2003 111
2 2003 1222

I would like to it have for eample for august:
Sum_of_august Sum_august-12_months

Is there any other way, than each time check in case statement when the period goes under 1 then minus year for one and starts the period from 12 again.

Regards



Pekka

"every dog will have his day

 

[rac2]

For the 12 months prior to and including a given period

DECLARE @as_of INT
SET @as_of = 8
SELECT SUM(Total)
FROM myTable
WHERE ( year = 2004 AND period <= @as_of )
   OR ( year = 2003 AND period >  @as_of )

 

[Pekka]

Thanks,
I found another approach to solve the problem:
first calculate the sequence number for each year/period group:
(year-1900)*12+period as datex
and to be compared it with current:
(year(getdate()-1900)*12+Month(getdate() as curdatex
Then something like
Select case when datex=curdatex-1 then total else 0 end as per_1,Select case when datex=curdatex-2 then total else 0 end as per_2,
.
.
Where datex between curdatex-11 and curdatex

ofcourse can declare variables for curdatex.


Pekka

"every dog will have his day