In this query (query table) along with complete year 2012 sales data, two months of 2013 sales data also exists. The query has been modified using the quarter query syntax to split the months into quarters based on the month as well as the year. The NTILE() cannot be used since it will split the 2013 data into quarter 1 and 2 instead of 1 both the months. Due to existence of two year data (2012 and 2013), the Ratio_To_Report function has to partition data by year for monthly ratio analysis (MONTHLY_%) -

RATIO_TO_REPORT(SUM(sales))
   OVER(PARTITION BY SUBSTR(q_dw.year_id,1,4))

The sum of percentages for 2012 will add to 100% and 2013 also will add to 100% but just for two months. In the quarterly ratio computation, the Ratio_To_Report function has to partition data by year and quarter for quarterly ratio analysis (QUARTERLY_%) -

-- RATIO_TO_REPORT(SUM(sales)) 
     OVER(PARTITION BY year, quarter)

RATIO_TO_REPORT(SUM(sales)) 
 OVER(PARTITION BY
  SUBSTR(q_dw.year_id,1,4),
  DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),4),
   TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
     DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),7),
      TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
        DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),10),
         TO_NUMBER(SUBSTR(q_dw.year_id,5)),4,3),2),1)
      )

For 2012, the Ratio_To_Report computes the value of
Jan/(Jan+Feb+Mar) + Feb/(Jan+Feb+Mar) + Mar/(Jan+Feb+Mar)
which will equal 100% (as shown below) ->
(11000/35000 + 12000/35000 + 14000/35000) = (0.297+0.324+0.378) = 1

For 2013, since there are just two month data, the the Ratio_To_Report computes the value of
Jan/(Jan+Feb) + Feb/(Jan+Feb) = 1
(14200/27450) + (13250/27450) = (0.517 + 0.483) = 1

The query output shows the above data and more .
    
SELECT   q_dw.year_id,
 -- TO_NUMBER(SUBSTR(q_dw.year_id,5)) month_No,
 DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),4),
  TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
    DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),7),
     TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
       DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),10),
        TO_NUMBER(SUBSTR(q_dw.year_id,5)),4,3),2),1) quarter,
 SUM(sales)  monthly,
 SUM(SUM(sales))
     OVER(PARTITION BY  SUBSTR(q_dw.year_id,1,4)
          ORDER BY TO_NUMBER(SUBSTR(q_dw.year_id,5))) ytd_sales,
 SUM(SUM(sales))
       OVER(PARTITION BY  SUBSTR(q_dw.year_id,1,4)
            ORDER BY SUBSTR(q_dw.year_id,1,4))  yearly_SUM,
 ROUND(RATIO_TO_REPORT(SUM(sales))   OVER(PARTITION BY
             SUBSTR(q_dw.year_id,1,4)),6)*100   "MONTHLY_%",
 SUM(SUM(sales))
   OVER(PARTITION BY   SUBSTR(q_dw.year_id,1,4),
     DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),4),
       TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
      DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),7),
        TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
       DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),10),
         TO_NUMBER(SUBSTR(q_dw.year_id,5)),4,3),2),1)
    ORDER BY 
      SUBSTR(q_dw.year_id,1,4), TO_NUMBER(SUBSTR(q_dw.year_id,5)) 
   ) quarterly,
 ROUND(RATIO_TO_REPORT(SUM(sales))
         OVER(PARTITION BY      SUBSTR(q_dw.year_id,1,4),
           DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),4),
            TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
            DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),7),
             TO_NUMBER(SUBSTR(q_dw.year_id,5)),  
             DECODE(GREATEST(TO_NUMBER(SUBSTR(q_dw.year_id,5)),10),
              TO_NUMBER(SUBSTR(q_dw.year_id,5)),4,3),2),1)
            ),6)*100 "QUARTERLY_%"
FROM
  (SELECT 201201 year_id, 11000  sales FROM DUAL UNION
   SELECT 201202 year_id, 12000  sales FROM DUAL UNION
   SELECT 201203 year_id, 14000  sales FROM DUAL UNION
   SELECT 201204 year_id, 16000  sales FROM DUAL UNION
   SELECT 201205 year_id, 15000  sales FROM DUAL UNION
   SELECT 201206 year_id, 18000  sales FROM DUAL UNION
   SELECT 201207 year_id, 19500  sales FROM DUAL UNION
   SELECT 201208 year_id, 17000  sales FROM DUAL UNION
   SELECT 201209 year_id, 16000  sales FROM DUAL UNION
   SELECT 201210 year_id, 16500  sales FROM DUAL UNION
   SELECT 201211 year_id, 13000  sales FROM DUAL UNION
   SELECT 201212 year_id, 15500  sales FROM DUAL UNION
   SELECT 201301 year_id, 14200  sales FROM DUAL UNION
   SELECT 201302 year_id, 13250  sales FROM DUAL
  ) q_dw
GROUP BY  q_dw.year_id,  TO_NUMBER(SUBSTR(q_dw.year_id,5))
ORDER BY q_dw.year_id;
-- Month/Quarter of year -> analysis output

Oracle registered trademark of Oracle Corporation.

Last revised on: April 12, 2013

  69952