Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
2.33/5 (3 votes)
See more:
I have a table one in which there are various attribute like region product,year,qtr,month,sale. I have to calculate the avg_qtr sale of each product having same region and show their previous avg_qtr sale.I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated. My table structure is like this

Region Product Year Qtr Month Sales

NORTH P1 2015 1 JAN 1000
NORTH P1 2015 1 FEB 2000
NORTH P1 2015 1 MAR 3000
NORTH P1 2015 2 APR 4000
NORTH P1 2015 2 MAY 5000
NORTH P1 2015 2 JUN 6000
NORTH P1 2015 3 JUL 7000
NORTH P1 2015 3 AUG 8000
NORTH P1 2015 3 SEP 9000
NORTH P1 2015 4 OCT 1000
NORTH P1 2015 4 DEC 4000
NORTH P1 2015 4 NOV 2000
NORTH P3 2015 1 FEB 1000
NORTH P3 2015 1 FEB 9000
NORTH P3 2015 2 APR 2000
NORTH P3 2015 3 JUL 8000
NORTH P1 2016 1 MAR 3000
NORTH P1 2016 1 FEB 1000
NORTH P1 2016 1 JAN 2000
SOUTH P1 2015 1 JAN 2000
SOUTH P1 2015 1 FEB 3000
SOUTH P1 2015 1 JAN 4000
SOUTH P2 2015 1 MAR 1000
SOUTH P2 2015 1 JAN 8000
SOUTH P2 2015 1 FEB 9000
SOUTH P2 2015 2 JUN 9000
SOUTH P2 2015 2 MAY 8000
SOUTH P2 2015 2 APR 2000
SOUTH P2 2015 3 SEP 4000
SOUTH P2 2015 3 AUG 2000
SOUTH P2 2015 3 JUL 1000
SOUTH P2 2015 4 NOV 2000
SOUTH P2 2015 4 DEC 1000
SOUTH P2 2015 4 OCT 5000
SOUTH P3 2015 3 AUG 9000
SOUTH P3 2015 4 OCT 1000
SOUTH P3 2015 4 NOV 3000
SOUTH P2 2016 1 JAN 2000
SOUTH P2 2016 1 JAN 4000

I wrote the query which calculates current qtr and is showing previous one avg with current one
SQL
  WITH AvgSales
AS (SELECT
region,
product,
year,
qtr,
ROUND(AVG(sales), 2) AS avg_Sale
FROM one
GROUP BY region,
product,
year,qtr
 )
SELECT
s.region,
s.product,
s.year,
s.month,
s.sales,
avg.qtr,
avg.avg_Sale AS Qtr_Avg_Sale,
prev.avg_sale AS Prev_Qtr_Avg_Sale
FROM one s
JOIN AvgSales avg
ON s.region = avg.region
AND s.product = avg.product
AND s.QTR = avg.qtr
AND s.year = avg.year
LEFT JOIN AvgSales prev
ON  (s.region = prev.region
AND s.product = prev.product
AND s.year - 1 = prev.year
and s.qtr=1
AND prev.qtr = 4) or
(s.region = prev.region
AND s.product = prev.product
AND s.year = prev.year
AND s.qtr - 1 = prev.qtr) ;


I am able to get current average and previous average of that product but not vice versa. I am not sure how to show the previous average of that quarter which does not have any sale in current quarter.
I want a output like this-



Region Product Year qtr month sale avg_Sale prev_avg_sale
NORTH P1 2015 1 JAN 1000 2000
NORTH P1 2015 1 FEB 2000 2000
NORTH P1 2015 1 MAR 3000 2000
NORTH P1 2015 2 APR 4000 5000 2000
NORTH P1 2015 2 MAY 5000 5000 2000
NORTH P1 2015 2 JUN 6000 5000 2000
NORTH P1 2015 3 JUL 7000 8000 5000
NORTH P1 2015 3 AUG 8000 8000 5000
NORTH P1 2015 3 SEP 9000 8000 5000
NORTH P1 2015 4 OCT 1000 2333.33 8000
NORTH P1 2015 4 NOV 2000 2333.33 8000
NORTH P1 2015 4 DEC 4000 2333.33 8000
SOUTH P2 2015 1 JAN 8000 6000
SOUTH P2 2015 1 FEB 9000 6000
SOUTH P2 2015 1 MAR 1000 6000
SOUTH P2 2015 2 APR 2000 6333.33 6000
SOUTH P2 2015 2 MAY 8000 6333.33 6000
SOUTH P2 2015 2 JUN 9000 6333.33 6000
SOUTH P2 2015 3 JUL 1000 2333.33 6333.33
SOUTH P2 2015 3 AUG 2000 2333.33 6333.33
SOUTH P2 2015 3 SEP 4000 2333.33 6333.33
SOUTH P2 2015 4 OCT 5000 2666.67 2333.33
SOUTH P2 2015 4 NOV 2000 2666.67 2333.33
SOUTH P2 2015 4 DEC 1000 2666.67 2333.33
NORTH P3 2015 1 FEB 9000 5000
NORTH P3 2015 1 FEB 1000 5000
NORTH P3 2015 2 APR 2000 2000 5000
NORTH P3 2015 3 JUL 8000 8000 2000
SOUTH P3 2015 3 AUG 9000 9000
SOUTH P3 2015 4 OCT 1000 2000 9000
SOUTH P3 2015 4 NOV 3000 2000 9000
NORTH P1 2016 1 JAN 2000 2000 2333.33
NORTH P1 2016 1 FEB 1000 2000 2333.33
NORTH P1 2016 1 MAR 3000 2000 2333.33
NORTH P2 2016 2 2000
SOUTH P2 2016 1 JAN 2000 3000 2666.67
SOUTH P2 2016 1 JAN 4000 3000 2666.67
SOUTH P2 2016 2 3000
SOUTH P1 2015 1 JAN 4000 3000
SOUTH P1 2015 1 JAN 2000 3000
SOUTH P1 2015 1 FEB 3000 3000
Posted
Updated 3-Sep-15 21:45pm
v3

Try this:
SQL
SELECT
    (SELECT AVG(Sales) FROM product WHERE year = @prevYear AND @QTR=@prevQtr) AS PrevQtr,
    (SELECT AVG(Sales) FROM product WHERE year = @currYear AND @QTR=@currQtr) AS CurrQtr

Replace @prevYear, @prevQtr, @currYear and @currYear with corresponding values.

Another option is to use Pivot[^] table.
 
Share this answer
 
Comments
[no name] 2-Sep-15 1:09am    
how it can be done by pivot??
[no name] 2-Sep-15 1:21am    
in this solution also how will I get every quarter avg value with current quarter
Maciej Los 2-Sep-15 1:59am    
Follow the link.
[no name] 2-Sep-15 2:01am    
I don't find any link
[no name] 2-Sep-15 2:00am    
where is the link?
You have two problems that needs to be solved here.
  1. Your table definition sucks.
    You have three different temporal types that doesn't sort properly.
  2. You have gaps in the sequence.

So lets fix them.
first we need to fix the temporal types
SQL
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.
SQL
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
SQL
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:
SQL
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.
 
Share this answer
 
v3
Comments
[no name] 4-Sep-15 3:46am    
I have updated my question please check it
Jörgen Andersson 4-Sep-15 4:00am    
Same principle, just add region wherever you have product in the query.
[no name] 4-Sep-15 4:29am    
No its not showing what I want now...I want if there is no sale in current quarter than it should show current sale 0 and previous avg sale in other column but your code does not show any this solve this issue
Jörgen Andersson 4-Sep-15 4:33am    
I don't understand, can you please elaborate
[no name] 4-Sep-15 4:38am    
please look at my output how I want it there you will find in 4 last row there is something like this-
SOUTH P2 2016 2 3000
here in south region product P2 has sale in first quarter of 2016 but there was none sale in 2016 2 quarter so it should show current_sale=0 and previous sale=3000(from 1 quarter)
with qtrs(qtr) as (select level from dual connect by level &lt;= 4)
, t1 as (
select region, product, year, q.qtr, month, sales, year*4+q.qtr qord
  from qtrs q
  left join one partition by (region, product, year)
    on q.qtr = one.qtr
)


select region
     , product
     , year
     , qtr
     , month
     , sales
     , round(avg(sales) over (partition by region, product, year,qtr),2) avg_sale
     , round(avg(sales) over (partition by region, product
                              order by qord
                              range between 1 preceding
                                        and 1 preceding),2) prev_avg_sale
  from t1
 order by year, region, qtr, product;
 
Share this answer
 
v3
Comments
Jörgen Andersson 7-Sep-15 10:16am    
A day when you learn something new is a good day.
I never knew about partitioned joins. Simplifies stuff immensely
[no name] 8-Sep-15 1:22am    
^_^ thnks!!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900