Rolling 12 months Query

[DataChick]

Hi There,

I am trying to create a rolling 12 month query for 24 months worth of data where each Period returns the sum of the last 12 months of data. My current SQL is as follows:

SELECT qryGroupedNewClm.Period,
Sum(IIf([Department]="Property" And [Claimsize] In ("WL","NPS"),1,0)) AS PropWLNPSClmCount,
Sum(IIf([Department]="Property" And [Claimsize] In ("LL","NPL"),1,0)) AS PropLLNPLClmCount
FROM qryGroupedNewClm
GROUP BY qryGroupedNewClm.Period;

This returns a count of claims in that period only - how do I get the query to sum the rolling 12 and display this per period?

Please help! thanks!

 

[Mr. B]

What kind of value is in the "Period" field?

You are going to have to be able to provide criteria that will only return records for the desired months. Not knowing any more about the structure of your data it is impossible to give a more definitive answer.

 

[DataChick]

Ok the two fields of interest are:

"Period" (Month of data captured) - "claimno" (identification of the claim).

I want to do a count of "claimno" for a rolling 12 and have the "period" field bring back the rolling 12 calculation. For example,

Currently my query is doing this (using 3 months as an example):

Period: claimno(count of period only)
2010/02 10
2010/01 15
2009/12 20

I want it to do this:

Period: SumR12:
2010/02 110 (count of 12 months prior)
2010/01 115
2009/12 120



Hope that makes more sense?

Thanks!