Hello, I am trying to determine average sales for the last 6 weeks using the following:
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
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:
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:
there would be 0 for average sales since the last 6 weeks are not represented.
Any help would be greatly appreciated.
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 ;
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.