7_QUERY_REPORTS.sql

-- SELECT ALL CUSTOMER
SELECT * FROM CUSTOMER C;
-- SELECT CUSTOMER FROM A REGION - EXAMPLE NORTH
SELECT * FROM CUSTOMER C WHERE C.CUST_REGION = 'N';


-- SELECT ALL PROGRAMS
SELECT * FROM PROGRAMS;
-- SELECT ACTIVE PROGRAM
SELECT * FROM PROGRAMS WHERE PROG_STATUS IN ('OPEN', 'DELAYED');
-- SEE PROGRAM BY STATUS
Select * FROM PROGRAMS WHERE PROG_STATUS =  'OPEN'; 
-- SELECT PROGRAM BY YEAR
SELECT * FROM PROGRAMS P WHERE 2016 BETWEEN YEAR(P.PROG_START) AND YEAR(P.PROG_END);
-- SELECT PROGRAM BY YEAR AND STATUS
SELECT * FROM PROGRAMS P WHERE 2016 BETWEEN YEAR(P.PROG_START) AND YEAR(P.PROG_END) AND PROG_STATUS = 'OPEN';


-- SELECT ALL EMPLOYEE
SELECT * FROM EMPLOYEE;
-- SELECT NOT ACTIVE EMPLOYEE
SELECT * FROM EMPLOYEE WHERE EMP_ENDDATE IS NOT NULL;

-- SELECT ALL PARTS
SELECT * FROM PART;
-- SELECT PART BY PART STATUS
SELECT * FROM PART WHERE STATUS = 'OPEN'; 
SELECT * FROM PART WHERE STATUS = 'CLOSED'; 
 -- SELECT PARTS BY COMMODITY
SELECT * FROM PART WHERE COMMODITY = 'FORGING'; 
-- SELECT COMMODITY AND STATUS
SELECT * FROM PART WHERE COMMODITY = 'FORGING' AND STATUS = 'OPEN'; 
SELECT * FROM PART WHERE COMMODITY = 'FORGING' AND STATUS = 'CLOSED'; 


-- SELECT ALL SUPPLIER
SELECT * FROM SUPPLIER;
-- SELECT SUPPLIER FROM REGION
SELECT * FROM SUPPLIER WHERE SUPP_REGION = 'N';


-- SELECT ALL SUPPLIER CONTACT
SELECT * FROM SUPPLIER_CONTACT;
-- SELECT A PARTICULAR SUPPLIER'S ALL REPRESENTATIVE'S CONTACT DETAILS
-- SELECT * FROM SUPPLIER_CONTACT WHERE SUPPLIER;
-- SELECT * PARTICULAR SUPPLIER'S PHONE NUMBER AND EMAIL ADDRESS
SELECT SUPP_EMP_NAME, SUPP_EMP_EMAIL, SUPP_EMP_PHONE
    FROM SUPPLIER_CONTACT WHERE SUPP_EMP_NAME REGEXP 'SOLAN HOLDEN';



SELECT * FROM PROGRAMS;
SELECT * FROM PROGRAM_PART;
SELECT * FROM PART;
-- NUMBER OF PARTICULAR PART REQUIRED FOR A PROGRAM
SELECT PROG_ID, PART_ID, QTY_FACTOR FROM PROGRAM_PART WHERE PROG_ID = 101;
SELECT PROG_ID, PART_ID, QTY_FACTOR FROM PROGRAM_PART WHERE PROG_ID = 101 AND PART_ID = 50001;
-- TOTAL NUMBER OF PARTS IN A PROGRAM
SELECT PROG_ID, COUNT(*) AS NO_OF_PARTS FROM PROGRAM_PART WHERE PROG_ID = 101 GROUP BY PROG_ID;
-- NUMBER OF PROGRAMS THAT NEED A PART WHOSE QUANTITY_FACTOR IS GREATER THAN 1
SELECT * FROM PROGRAM_PART WHERE QTY_FACTOR > 1;


-- SELECT ALL OUTGOING INVOICES
SELECT * FROM OUTGOING_INVOICE;
-- SELECT NUMBER OF OUTGOING PARTS(QUANTITY) IN THE BATCH
SELECT * FROM OUTGOING_INVOICE WHERE OUT_INV_NO = 201902;
-- SELECT NUMBER OF OUTGOING PARTS FOR ALL BATCHES FOR A PPID 
SELECT * FROM OUTGOING_INVOICE WHERE PROG_PART_ID = 60001;
-- SELECT NUMBER OF OUTGOING PARTS FOR A PPID FOR THE LATEST BATCH
SELECT * FROM OUTGOING_INVOICE WHERE 
    OUT_INV_DATE = (SELECT MAX(OUT_INV_DATE)FROM OUTGOING_INVOICE WHERE PROG_PART_ID = 60001);
-- SELECT ALL OUTGOING INVOICE FOR A PARTICULAR YEAR
SELECT * FROM OUTGOING_INVOICE WHERE 
    EXTRACT(YEAR FROM OUT_INV_DATE) = 2018;
-- SELECT ALL OUTGOING INVOICE FOR A PPID FOR A PARTICULAR YEAR
SELECT * FROM OUTGOING_INVOICE WHERE 
    EXTRACT(YEAR FROM OUT_INV_DATE) = 2018 AND PROG_PART_ID = 60001;
-- TOTAL QUANTITY DISPATCHED FOR A PARTICULAR PPID
SELECT PROG_PART_ID, SUM(QUANTITY) AS TOTAL_QTY_DISPATCHED FROM OUTGOING_INVOICE 
    WHERE PROG_PART_ID = 60001 GROUP BY PROG_PART_ID;
-- SELECT TOTAL NUMBER OF INVOICES IN A MONTH AND YEAR
SELECT * FROM OUTGOING_INVOICE WHERE 
    EXTRACT(MONTH FROM OUT_INV_DATE)= 08 AND EXTRACT(YEAR FROM OUT_INV_DATE)= 2018;


-- SELECT ALL EMPLOYEE_PROGRAMS
SELECT * FROM EMPLOYEE_PROGRAM;
-- SELECT THE START AND END DATE OF AN EMPLOYEE FOR ALL PROGRAMS ASSOCIATED WITH HIM/HER
SELECT EMP_ID, PROG_ID, START_DATE, END_DATE FROM EMPLOYEE_PROGRAM WHERE EMP_ID = 123458;
-- SELECT EMPLOYEE_ID OF A PROGRAM MANAGER
SELECT * FROM EMPLOYEE_PROGRAM WHERE DESIGNATION = 'PROGRAM MANAGER';
-- TOTAL NUMBER OF EMPLOYEE IN A PROGRAM
SELECT * FROM EMPLOYEE_PROGRAM WHERE PROG_ID = 102;
SELECT PROG_ID, COUNT(*) AS NO_OF_EMPLOYEE FROM EMPLOYEE_PROGRAM WHERE PROG_ID = 102 GROUP BY PROG_ID;
-- FINDING ALL ACTIVE EMPLOYEE PROGRAM AS OF TODAY 
SELECT * FROM EMPLOYEE_PROGRAM WHERE END_DATE >= SYSDATE() OR END_DATE IS NULL;
-- FINDING EMPLOYEE WHO ARE NOT ASSOCIATED WITH ANY PROGRAM AS OF TODAY
SELECT * FROM EMPLOYEE_PROGRAM WHERE END_DATE < SYSDATE();


-- APPROVAL_DETAILS
SELECT * FROM APPROVAL_DETAILS;
-- SELECT ALL APPROVAL_DETAILS FOR A PARTICULAR PART_ID
SELECT * FROM APPROVAL_DETAILS WHERE PART_ID = 50002;
-- SELECT ALL PART APPROVAL DETAILS FOR A PARTICULAR SUPPLIER
SELECT * FROM APPROVAL_DETAILS WHERE SUPP_ID = 305;
-- SELECT ALL APPROVAL DETAILS FOR A PART FROM A PARTICULAR SUPPLIER
SELECT * FROM APPROVAL_DETAILS WHERE PART_ID = 50002 AND SUPP_ID = 305;
-- SELECT ALL APPROVAL DATE FOR A PART ID
SELECT PART_ID, APP_DATE FROM APPROVAL_DETAILS WHERE PART_ID = 50002;
-- SELECT ALL SQE FOR A SUPPLIER
SELECT SUPP_ID, SQE_REP FROM APPROVAL_DETAILS WHERE SUPP_ID = 305;
-- SELECT APPROVAL COST FOR A PART
SELECT PART_ID, APP_STATUS, APP_COST FROM APPROVAL_DETAILS WHERE PART_ID = 50002;
-- SELECT ALL APPROVED STATUS
SELECT * FROM APPROVAL_DETAILS WHERE APP_STATUS = 'APPROVED';
-- SELECT ALL PENDING STAUS
SELECT * FROM APPROVAL_DETAILS WHERE APP_STATUS = 'PENDING';
-- SELECT ALL APPROVAL IN A YEAR
SELECT * FROM APPROVAL_DETAILS WHERE EXTRACT(YEAR FROM APP_DATE) = 2019;

-- PURCHASE_ORDER
-- SELECT ALL PURCHASE_ORDER
SELECT * FROM PURCHASE_ORDER;
-- SELECT A PARTICULAR PURCHASE_ORDER DATE FOR APPROVAL_ID
SELECT PART_APPR_ID, PO_DATE FROM PURCHASE_ORDER WHERE PART_APPR_ID = 4003;
-- SELECT ITEM_QUANTITY FOR A PARTICULAR PART_APPR_ID
SELECT PART_APPR_ID, ITEM_QTY FROM PURCHASE_ORDER WHERE PART_APPR_ID = 4003;


-- ---------------------------------------------- QUERRIES-----------------------------------------------------------------

SELECT * FROM PROJECT_BY_CUSTOMER;

-- FIND CUSTOMER WHO NEVER STARTED A PROGRAM
SELECT DISTINCT CUSTOMER_ID, CUSTOMER_NAME  FROM PROJECT_BY_CUSTOMER WHERE PROGRAM_ID IS NULL;

    
-- COUNT NUMBER OF PROJECTS PER CUSTOMER
SELECT 
    CUSTOMER_ID, 
    CUSTOMER_NAME,
    COUNT(*) AS NO_OF_PROJECTS, 
    SUM(MAX_PROD_WEEKLY) AS TOTAL_MAX_PROD_WEEKELY, 
    SUM(AVG_PROD_WEEKLY) AS TOTAL_AVG_PROD_WEEKLY  
    FROM 
        PROJECT_BY_CUSTOMER
        WHERE PROGRAM_ID IS NOT NULL
            GROUP BY customer_id, CUSTOMER_NAME
            ORDER BY COUNT(*) DESC;
            
SELECT * FROM APPROVAL_WITH_EMPLOYEE;

-- FIND AVERAGE TIME TAKEN PER EMPLOYE TO EVALUATUE FOR APPROVAL
SELECT 
    APP_EMP_ID, 
    EMP_NAME, 
    ROUND(AVG(period_diff(EXTRACT(YEAR_MONTH FROM STATUS_DATE),EXTRACT(YEAR_MONTH FROM SUBMIT_DATE)))) AS AVG_EVAL_TIME_MONTHS 
FROM APPROVAL_WITH_EMPLOYEE 
    WHERE STATUS != 'PENDING'
    GROUP BY APP_EMP_ID, EMP_NAME
    ORDER BY AVG_EVAL_TIME_MONTHS;

-- SELECT EMPLOYEE WITH TOTAL PRODUCT EVALUATIONS
SELECT 
    APP_EMP_ID, 
    EMP_NAME, 
    COUNT(*) AS EVALUATIONS 
FROM APPROVAL_WITH_EMPLOYEE 
    GROUP BY APP_EMP_ID, EMP_NAME
    ORDER BY COUNT(*) DESC;

-- GET NUMBER OF APPROVED PROJECTS BY EMPLOYEE
SELECT APP_EMP_ID, EMP_NAME, COUNT(*) AS APPROVED FROM APPROVAL_WITH_EMPLOYEE
WHERE STATUS = 'APPROVED'
GROUP BY APP_EMP_ID, EMP_NAME
ORDER BY APPROVED DESC;

-- GET NUMBER OF DENIED PROJECT BY EMPLOYEE
SELECT APP_EMP_ID, EMP_NAME, COUNT(*) AS DENIED FROM APPROVAL_WITH_EMPLOYEE
WHERE STATUS = 'DENIED'
GROUP BY APP_EMP_ID, EMP_NAME
ORDER BY DENIED DESC;

-- GET NUMBER OF PENDING PROJECT BY EMPLOYEE
SELECT APP_EMP_ID, EMP_NAME, COUNT(*) AS PENDING FROM APPROVAL_WITH_EMPLOYEE
WHERE STATUS = 'PENDING'
GROUP BY APP_EMP_ID, EMP_NAME
ORDER BY PENDING DESC;

-- GET ALL EVAUATION STATS
SELECT * FROM EVALUATION_STATS_EMPLOYEE;

SELECT * FROM APPROVAL_WITH_SUPPLIER;

-- GET APPROVED PROJECT BY SUPPLIER
SELECT 
    SUPPLIER_ID,
    SUPPLIER_NAME,
    COUNT(*) AS APPROVED 
FROM APPROVAL_WITH_SUPPLIER
    WHERE STATUS = 'APPROVED'
    GROUP BY SUPPLIER_ID,SUPPLIER_NAME
    ORDER BY APPROVED DESC;
    
-- GET PENDING APPROVALS BY SUPPLIER
SELECT 
    SUPPLIER_ID,
    SUPPLIER_NAME,
    COUNT(*) AS PENDING 
FROM APPROVAL_WITH_SUPPLIER
    WHERE STATUS = 'PENDING'
    GROUP BY SUPPLIER_ID,SUPPLIER_NAME
    ORDER BY PENDING DESC;

-- GET DENIED APPROVALS WITH EMPLOYEE
SELECT 
    SUPPLIER_ID,
    SUPPLIER_NAME,
    COUNT(*) AS DENIED 
FROM APPROVAL_WITH_SUPPLIER
    WHERE STATUS = 'DENIED'
    GROUP BY SUPPLIER_ID,SUPPLIER_NAME
    ORDER BY DENIED;

-- GET EVALUATION STATS BASED ON SUPPLIER    
SELECT * FROM EVALUATION_STATS_SUPPLIER;


    
SELECT * FROM PART_APPROVAL_SUPPLIER;

-- GET ALL ACTIVE SUPPLIERS BY PART
SELECT
    PAS.PART_ID,
    PAS.PART_NAME,
    PAS.PART_STATUS,
    PAS.SUPPLIER_NAME,
    ASUP.EMPLOYEE_NAME,
    PAS.SUPPLIER_ID,
    ASUP.EMAIL,
    ASUP.PHONE_NO
FROM
    PART_APPROVAL_SUPPLIER PAS 
    LEFT OUTER JOIN ACTIVE_SUPPLIER ASUP
    ON PAS.SUPPLIER_ID = ASUP.SUPPLIER_ID
    ORDER BY PAS.PART_ID;

-- GET NUMBER OF ACTIVE SUPPLIERS PER PART
SELECT 
    PART_ID , 
    PART_NAME,
    COUNT(*) NUMBER_OF_SUPPLIERS
FROM PART_APPROVAL_SUPPLIER 
    WHERE PART_STATUS = 'OPEN' AND APPROVAL_STATUS = 'APPROVED'
    GROUP BY PART_ID, PART_NAME
    ORDER BY COUNT(*) DESC;

-- GET ALL ACTIVE SUPPLIER QITH CONTACT DETAILS    
SELECT * FROM ACTIVE_SUPPLIER;

SELECT * FROM INVENTORY_FLOW_STATUS;


-- GET ALL EMPLOYEES ASSOCIATED TO A PROGRAM
SELECT 
    P.PROG_ID, 
    P.PROG_NAME,
    P.CUST_ID,
    EMP.EMP_ID,
    EMP.EMP_NAME,
    EMP.DESIGNATION
FROM PROGRAMS P
    LEFT JOIN(
        SELECT 
            E.EMP_NAME ,
            EP.DESIGNATION,
            EP.EMP_PROG_ID, 
            EP.EMP_ID, 
            EP.PROG_ID  
        FROM EMPLOYEE_PROGRAM EP 
            LEFT JOIN EMPLOYEE E 
            ON EP.EMP_ID = E.EMP_ID
    ) EMP
    ON P.PROG_ID = EMP.PROG_ID
    ORDER BY P.PROG_ID;

-- GET ALL PROJECT BY CUSTOMER
SELECT * FROM PROJECT_BY_CUSTOMER;


-- GET ALL EMPLOYEE WITH A PROJECT
SELECT 
    PROGRAM_ID,
    PROGRAM_NAME,
    CUSTOMER_NAME,  
    EMP_ID,
    EMP_NAME,
    EMP_START_DATE,
    DESIGNATION
FROM CUSTOMER_PROGRAM_EMPLOYEE 
    WHERE PROGRAM_ID = 104;
    
-- GET EMPLOYEE ROLES COUNT
SELECT 
    EMP_ID,
    EMP_NAME,
    COUNT(*) NO_OF_ROLES
FROM CUSTOMER_PROGRAM_EMPLOYEE
    WHERE
    EMP_ID IS NOT NULL
    GROUP BY
    EMP_ID,
    EMP_NAME
    ORDER BY NO_OF_ROLES DESC;


-- GET ALL CUSTOMER AND THEIR CORESPONDING PROGRAMS WITH PARTS
SELECT * FROM CUSTOMER_PROGRAM_PART;

-- GET NUMBER OF PARTS AND TOTAL VOLUME USED BY EACH CUSTOMER
SELECT 
    CUSTOMER_ID, 
    CUSTOMER_NAME, 
    COUNT(PART_ID) AS NO_OF_PARTS,
    SUM(OUT_APW) AS TOTAL_OUT_APW,
    SUM(OUT_MPW) AS TOTAL_OUT_MPW
FROM CUSTOMER_PROGRAM_PART
    GROUP BY CUSTOMER_ID, CUSTOMER_NAME
    ORDER BY NO_OF_PARTS DESC
;

SELECT * FROM CUSTOMER_PROGRAM_INVOICE;

-- GET INCOME PER PROJECT
SELECT 
    PROGRAM_ID, 
    PROGRAM_NAME, 
    SUM(TOTAL_COST),0 AS INCOME 
FROM CUSTOMER_PROGRAM_INVOICE
    WHERE PROGRAM_ID IS NOT NULL
    GROUP BY PROGRAM_ID, PROGRAM_NAME
    ORDER BY INCOME DESC;


-- GET INCOME PER CUSTOMER
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    SUM(TOTAL_COST),0 AS INCOME 
FROM CUSTOMER_PROGRAM_INVOICE
    WHERE PROGRAM_ID IS NOT NULL
    GROUP BY CUSTOMER_ID, CUSTOMER_NAME
    ORDER BY INCOME DESC;