Database
Fan

## Determining Average Sales for the Last 6 Weeks using Over() Partition

Hello, I am trying to determine average sales for the last 6 weeks using the  following:

select
b.FISC_EOW_DT,
a.*,
avg(net_unit_qty) over (partition by sid order by FISC_EOW_DT rows between 5 preceding and current row) as avsaleslast6wk

FROM tbl1 a
JOIN (SELECT DISTINCT FISC_WK_OF_MTH_ID,FISC_EOW_DT FROM tbl2 ) B
ON B.FISC_WK_OF_MTH_ID=A.FISC_WK_OF_MTH_ID
where sid = 12345

This works however it calculates the last 5 rows, regardless if they are the previous week or not. So for example:

if the weeks was:

12/01/2016

01/08/2017

06/01/2017

08/01/2017

It will calculate the average of these 4 weeks even though they are not consecutive. I need to know how to calculate the Average sales including weeks that are not consecutive. So for the week going back from:

01/08/2017

to

06/01/2017

there would be 0 for average sales since the last 6 weeks are not represented.

Any help would be greatly appreciated.

2 REPLIES
Senior Supporter

## Re: Determining Average Sales for the Last 6 Weeks using Over() Partition

as the syntax states - and you already noticed - it is dealing with rows and not with periods.

"rows between 5 preceding and current row"

the documentation refer this as "Ensure that data you analyze has no missing data points. Computing a moving function over data with
missing points produces unexpected and incorrect results because the computation considers n physical
rows of data rather than n logical data points."

This means you have to create the missing week infos yourself. try something like

```SELECT b.FISC_EOW_DT,
a.*,
AVG(zeroifnull(a.net_unit_qty) OVER (PARTITION BY b.sid ORDER BY b.FISC_EOW_DT rows BETWEEN 5 preceding AND CURRENT row) AS avsaleslast6wk
FROM tbl1 a
right outer JOIN
(SELECT t1.sid, t2.FISC_WK_OF_MTH_ID, t2.FISC_EOW_DT
FROM tbl2 as t2
cross join
select t2.sid
from tbl1 as t1
group by t1.sid, t2.FISC_WK_OF_MTH_ID, t2.FISC_EOW_DT
) B
ON a.sid = b.sid
and B.FISC_WK_OF_MTH_ID = A.FISC_WK_OF_MTH_ID
WHERE sid = 12345
qualify a.sid is not null
;```

Highlighted
Fan

## Re: Determining Average Sales for the Last 6 Weeks using Over() Partition

Hello,

Thank you for the reply. You are right, I figured I will need to populate the months so that there are no inconsistencies in the dates. I have tried to run the code that you provided and it is kicking out the error:

`SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'join' keyword and the 'select' keyword. `

I think this is in reference to the cross join, but when I try to close the statement it still gives an error. Also, t2 does not have sid in the table, I noticed this in the code. Thank you for you help.