Home » SQL & PL/SQL » SQL & PL/SQL » 4 Week rolling average.. is this possible?
4 Week rolling average.. is this possible? [message #339811] Fri, 08 August 2008 12:31 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
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.

[Updated on: Fri, 08 August 2008 12:42] by Moderator

Report message to a moderator

Re: 4 Week rolling average.. is this possible? [message #339812 is a reply to message #339811] Fri, 08 August 2008 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use AVG but just keep the last 4 weeks maybe using CASE/DECODE maybe using analytic form.

For more, post a test case: create table and insert statements along with the result you want with these data.

And keep your lines in 80 characters.


Regards
Michel

[Updated on: Fri, 08 August 2008 12:43]

Report message to a moderator

Re: 4 Week rolling average.. is this possible? [message #339813 is a reply to message #339811] Fri, 08 August 2008 12:45 Go to previous message
szogu
Messages: 21
Registered: July 2008
Junior Member
Analytical functions is what you need

Regards,
Previous Topic: PLS-00382: expression is of wrong type
Next Topic: DISTINCT ROWNUM
Goto Forum:
  


Current Time: Sun Dec 04 00:19:43 CST 2016

Total time taken to generate the page: 0.07151 seconds