You have two problems that needs to be solved here.
- Your table definition sucks.
You have three different temporal types that doesn't sort properly. - You have gaps in the sequence.
So lets fix them.
first we need to fix the temporal types
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
That was easy, filling the gaps is worse.
First we find the first and last dates used and then we create a sequence out of that.
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
Now we create a dummytable with all months and join with the fixed table
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
,datesequence AS (
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT DISTINCT
region
,product
FROM one
)
,dummytable AS (
SELECT region,product,saledate,0 sales
FROM products p,datesequence ds
)
SELECT d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM dummytable d
left OUTER JOIN fixeddate f
ON d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate
Now we can query a table with a proper layout:
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
,datesequence AS (
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT DISTINCT
region
,product
FROM one
)
,dummytable AS (
SELECT region,product,saledate,0 sales
FROM products p,datesequence ds
)
,fixedtableone AS (
SELECT d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM dummytable d
left OUTER JOIN fixeddate f
ON d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate
)
,withavg AS (
SELECT region
,product
,saledate
,sales
,Round(Avg(sales) OVER (PARTITION BY region,product,To_Char(saledate,'YYYY-Q') )) qtr_avg
FROM fixedtableone
)
SELECT region
,product
,To_Char(saledate,'YYYY') year
,To_Char(saledate,'Q') qtr
,To_Char(saledate,'MON') MONTH
,sales
,qtr_avg
,Lag(qtr_avg,3,0) OVER (PARTITION BY region,product ORDER BY saledate) prv_qtr_avg
FROM withavg
You can test the query
here[
^].
With a proper table layout you would have only needed the last two parts of the query and it would have scaled a lot better too.