4 Week rolling average.. is this possible? 1

[fmrock]

Hey Everyone,

I have request, that i have no idea on how to do this in SQL and I am thinking this may have to be an application that does this.

I have the following query.

SELECT 
	CHARGES.CPCODE Site, 
	CHARGES.BILLSTATUS FC,
	Sum(CHARGES.PRAMOUNT) Payment_Amount,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
FROM 
	CHARGES CHARGES
WHERE
	(CHARGES.TYPE='P') 
	AND (CHARGES.SPLITFLAG IS  NULL)
	AND CHARGES.POSTDATE BETWEEN to_number(to_char(trunc(sysdate,'W'),'J')-(52*7)) AND to_number(to_char(trunc(sysdate,'W'),'J'))
GROUP BY
	CHARGES.CPCODE,
	CHARGES.BILLSTATUS,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

This sums up the amount of payments we get per location per financial class.

Pretty simple right? Now we have the request to create a 4 week rolling average. Basiclly for each week, we want to average the last 4 weeks, except for the most current week, it should not do the average.

is this possible? Any ideas or suggestions.

 

[fmrock]

Works great. Thanks a bunch!