SQL & PL/SQL

Hello There,

I have a dynamic view build which produces a results calendar month wise as below:

CREATE OR REPLACE PROCEDURE create_view_period_matrix (p_total_period NUMBER)

AS

CURSOR c_qry

IS

SELECT  TO\_CHAR (ADD\_MONTHS (SYSDATE, LEVEL - 2), 'MM')              AS MonthNumber

,             TO\_CHAR (ADD\_MONTHS (SYSDATE, LEVEL - 2), 'Mon-YYYY')   AS MonthYear

FROM    dual

CONNECT BY  LEVEL  \<= p\_total\_period;

l_period_names VARCHAR2(4000);

l_q CHAR(1) := '''';

l_statement VARCHAR2(2000);

BEGIN

FOR cell IN c_qry

LOOP

    l\_period\_names := l\_period\_names||l\_q||cell.MonthNumber||l\_q||' as "'||cell.MonthNumber||'",';

END LOOP;

l_period_names := SUBSTR(l_period_names, 1, LENGTH(l_period_names)-1);

l_statement := ('SELECT * '||

                      'FROM   (SELECT  TO\_CHAR (ADD\_MONTHS (SYSDATE, LEVEL - 2), ''MM'')         AS MonthNumber  '||

                      '        ,       TO\_CHAR (ADD\_MONTHS (SYSDATE, LEVEL - 2), ''Mon-YYYY'')   AS MonthYear '||

                      '        FROM DUAL '||

                      '        CONNECT BY  LEVEL  \<= '|| p\_total\_period||

                      '       ) s  '||

                      'PIVOT  (MAX(MonthYear) FOR MonthNumber IN ('||l\_period\_names||'))');

EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW period_matrix AS '||l_statement;

END;

--Step 2

BEGIN

create_view_period_matrix(12); --create for 12 months

END;

--Step 3

SELECT *

FROM period_matrix;

RESULT

I have a employee table as below:

--Step 4

CREATE TABLE employee

(emp_name VARCHAR2(100)

,start_date DATE

, end_date DATE);

--Step 5

INSERT INTO employee VALUES('Ron' ,'01-JUL-2016' , '31-DEC-016');
INSERT INTO employee VALUES('Don' ,'01-JAN-2016' , '25-OCT-2016');
INSERT INTO employee VALUES('David' ,'01-AUG-2016' , '16-NOV-2016');
INSERT INTO employee VALUES('Alan' ,'01-SEP-2016' , '30-APR-2017');

--Step 6

SELECT * FROM employee

The below result I would like to have connecting the above (step 3) view and table (step 4):

**--DESIRED FINAL OUTPUT (combining view period_matrix and table employee)
**

Columns 07, 08, 09..., 01, 02.... denotes month number, eg. 07=July-2016, 08=Aug-2016, .... 01=Jan-2017, 02=Feb-2017.... From the view (step 3)

For example: Ron's Start_Date and End_Date is 01-Jul-2016 and 31-Dec-2016.

                In the matrix under columns 07 to 12, it should display 1, else 0

I would like to base the above final out using a view by combining the . Could you please advise, how this can be achieved?

Many thanks and look forward for your input.

Regards,

Don