SQL & PL/SQL

Hello All,

I need a help on writing a SQL query (Oracle 11 G) here is the details.

We have two tables. Please note I could not provide the Production table structures so sharing this dummy but similar table structures with dummy data

1. T_PRODUCT_SALES - which store product sales amount. Below is the structure and sample data

    DDL -- Create table

     CREATE TABLE T_PRODUCT_SALES_UNIT

       ( PRODUCT_ID                VARCHAR2(10), --'Product Id'

         MONTH_DESC                VARCHAR2(10), --'Month Name Month-YYYY (Character)'

         UNIT_SOLD_COUNT           VARCHAR2(10), --'Count of Units sold'

         PRODUCT_SOLD_MONTH_YYYYMM VARCHAR2(10) --'Numeric in the format YYYYMM'

       );

  Sample Insert script ---- Insert Script

     INSERT INTO T_PRODUCT_SALES (PRODUCT_ID, MONTH_DESC, SALES_AMOUNT, PRODUCT_SOLD_MONTH_YYYYMM)

       VALUES ('100', 'January-2016', '10000', '201601');

     INSERT INTO T_PRODUCT_SALES (PRODUCT_ID, MONTH_DESC, SALES_AMOUNT, PRODUCT_SOLD_MONTH_YYYYMM)

       VALUES ('200', 'January-2016', '1000', '201601');

     INSERT INTO T_PRODUCT_SALES (PRODUCT_ID, MONTH_DESC, SALES_AMOUNT, PRODUCT_SOLD_MONTH_YYYYMM)

       VALUES ('100', 'February-2016', '20000', '201602');

     INSERT INTO T_PRODUCT_SALES (PRODUCT_ID, MONTH_DESC, SALES_AMOUNT, PRODUCT_SOLD_MONTH_YYYYMM)

       VALUES ('200', 'February-2016', '2000', '201602');

     COMMIT;

  2. T_PRODUCT_SALES_UNIT - which store product sales quantity. Below is the structure and sample data

  DDL -  -- Create table

     CREATE TABLE T_PRODUCT_SALES_UNIT

       ( PRODUCT_ID                VARCHAR2(10), --'PRODUCT ID'

         MONTH_DESC                VARCHAR2(10), --'MONTH NAME MONTH-YYYY (CHARACTER)'

         UNIT_SOLD_COUNT           VARCHAR2(10), --'COUNT OF UNITS SOLD'

         PRODUCT_SOLD_MONTH_YYYYMM VARCHAR2(10)  --'NUMERIC IN THE FORMAT YYYYMM'

      );

  Sample Insert script -- Insert Script

    INSERT INTO T_PRODUCT_SALES_UNIT (PRODUCT_ID, MONTH_DESC, UNIT_SOLD_COUNT, PRODUCT_SOLD_MONTH_YYYYMM)

            VALUES ('100', 'January-2016', '100', '201601');

    INSERT INTO T_PRODUCT_SALES_UNIT (PRODUCT_ID, MONTH_DESC, UNIT_SOLD_COUNT, PRODUCT_SOLD_MONTH_YYYYMM)

            VALUES ('200', 'January-2016', '10', '201601');

    INSERT INTO T_PRODUCT_SALES_UNIT (PRODUCT_ID, MONTH_DESC, UNIT_SOLD_COUNT, PRODUCT_SOLD_MONTH_YYYYMM)

            VALUES ('100', 'February-2016', '200', '201602');

    INSERT INTO T_PRODUCT_SALES_UNIT (PRODUCT_ID, MONTH_DESC, UNIT_SOLD_COUNT, PRODUCT_SOLD_MONTH_YYYYMM)

            VALUES ('200', 'February-2016', '20', '201602');

    COMMIT;

Both the tables has a composite primary key (PRODUCT_ID ,PRODUCT_SOLD_MONTH_YYYYMM) and T_PRODUCT_SALES is the parent table where as T_PRODUCT_SALES_UNIT is the child table

Problem Statement -

Using the existing data ( we have only for Jan and Feb 2016) we have to prepare a Select query which will return results forecasting sales quantity and sales $ value for next 12 months ( i.e. from March 2016 to  Feb 2017). The formula for forecasting the Sales is as below

forecasted Sales ($)   =  Avg of previous two month's Sales value (Actual or forecasted)+ 10% growth

forecasted Sales Unit  = Avg of previous two month's Sales Unit (Actual or forecasted) + 10% growth

Below is the desired result

Thanks in advance for the help!

Thanks & Regards

SB.