My rolling average isn't working correctly

See more:

SQL

Oracle

AVG

I’m trying to write a query to get the totals for the current day, the previous day, the difference between current day and yesterday and the rolling average for that month by foreman . So far I have the current day, previous day and the difference but the average part is what’s eluding my. What I have so far works if your date range is within a single month. Once you go beyond the a single month, the query continues to sum up the each day thereafter thus giving me incorrect numbers.

Here’s what I’m looking at:

FOREMAN		DATE		TODAY OIL VOLS	TODAY GAS VOLS	PREV OIL VOLS		PREV GAS VOLS	DIFF OIL	DIFF  GAS	MTD OIL AVG
BART SIMPSON		1/1/2014	12			20			23			40			-11		-20		12
BART SIMPSON		1/2/2014	15			19			12			20			3		-1		13.5
BART SIMPSON		1/3/2014	25			30			15			19			10		11		17.3
BART SIMPSON		2/1/2014	20			15			25			30			-5		-15		72
BART SIMPSON		2/2/2014	45			35			20			15			15		20		58.5
BART SIMPSON		2/3/2014	50			40			45			35			5		5		55.6
LISA SIMPSON		1/1/2014	50			10			45			5			5		5		217
LISA SIMPSON		1/2/2014	60			4			50			10			10		-6		138.5
LISA SIMPSON		1/3/2014	42			20			60			4			-18		16		106.3
LISA SIMPSON		2/1/2014	50			10			42			20			5		5		369
LISA SIMPSON		2/2/2014	55			4			50			10			10		-6		212
LISA SIMPSON		2/3/2014	60			20			55			4			-18		16		161.3

I would seem that once the query moves onto the next foreman the query continues to sum all six TODAY OIL VOLS from BART SIMPSON and add LISA SIMPSON 1/1/2014 entry (12+15+25+20+45+50+50=217) and divides it by 1. It continues to sum up the TODAY OIL VOLS and divides it by the number of days…12+15+25+20+45+50+50+60=277…277/2 = 138.5

Below is what I’m wanting to achieve:

FOREMAN			DATE		TODAY OIL VOLS	TODAY GAS VOLS	PREV OIL VOLS	PREV GAS VOLS	DIFF OIL	DIFF  GAS	MTD OIL AVG
BART SIMPSON		1/1/2014	12			20			23			40			-11		-20		12
BART SIMPSON		1/2/2014	15			19			12			20			3		-1		13.5
BART SIMPSON		1/3/2014	25			30			15			19			10		11		17.3
BART SIMPSON		2/1/2014	20			15			25			30			-5		-15		20
BART SIMPSON		2/2/2014	45			35			20			15			15		20		32.5
BART SIMPSON		2/3/2014	50			40			45			35			5		5		38.3
LISA SIMPSON		1/1/2014	50			10			45			5			5		5		50
LISA SIMPSON		1/2/2014	60			4			50			10			10		-6		55
LISA SIMPSON		1/3/2014	42			20			60			4			-18		16		50.6
LISA SIMPSON		2/1/2014	50			10			42			20			5		5		50
LISA SIMPSON		2/2/2014	55			4			50			10			10		-6		52.5
LISA SIMPSON		2/3/2014	60			20			55			4			-18		16		55

I hope that all makes sense. I could really use some help fixing my query (below) to achieve the above result.

Thank you in advance.

Jake

SELECT
    WELL.FOREMAN_NM
    ,CUR.PROD_DT
    ,ROUND(SUM(CUR.VO_OIL_PROD)) AS TODAY_OIL_VOLS
    ,ROUND(SUM(CUR.VO_GAS_PROD)) AS TODAY_GAS_VOLS
    ,ROUND(SUM(PREV.PREV_VO_OIL_PROD)) AS PREV_OIL_VOLS
    ,ROUND(SUM(PREV.PREV_VO_GAS_PROD)) AS PREV_GAS_VOLS
    ,ROUND(SUM(CUR.VO_OIL_PROD)) - ROUND(SUM(PREV.PREV_VO_OIL_PROD)) AS DIFF_OIL
    ,ROUND(SUM(CUR.VO_GAS_PROD)) - ROUND(SUM(PREV.PREV_VO_GAS_PROD)) AS DIFF_GAS
    ,ROUND(SUM(SUM(CUR.VO_OIL_PROD)) OVER (ORDER BY WELL.FOREMAN_NM, CUR.PROD_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)/EXTRACT(DAY FROM TO_DATE(CUR.PROD_DT))) AS MTD_OIL_AVG

FROM WELL

    INNER JOIN CUR
        ON WELL.PID = CUR.PID
        
    INNER JOIN (SELECT PID, PROD_DT, VO_OIL_PROD AS PREV_VO_OIL_PROD, VO_GAS_PROD AS PREV_VO_GAS_PROD
                FROM CUR) PREV
        ON CUR.PID = PREV.PID
       AND PREV.PROD_DT = TRUNC(CUR.PROD_DT-1,'DD')

WHERE 
    WELL.FOREMAN_NM IN ('BART SIMPSON','LISA SIMPSON')
  AND  CUR.PROD_DT >= TO_DATE('01/01/2014','MM/DD/YYYY')
  AND  CUR.PROD_DT <= TO_DATE('02/28/2014','MM/DD/YYYY')
GROUP BY WELL.FOREMAN_NM, CUR.PROD_DT
ORDER BY 1,2