PL/SQL procedure to dynamically pass the value

Question

WITH CTE AS
(SELECT 
F.PARTITION_KEY,
(LAST_DAY(TO_DATE((PARTITION_KEY)||'01','YYYYMMDD'))) AS VALID_DATE,
F.EMP_NUM,
F.EMP_KEY,
D.EMP_ENTERED_DATE 
FROM F_EMP_ERROR, D_EMP D
WHERE 
F.EMP_NUM = D.EMP_NUM
AND F.PARTITION_KEY = 201603
and d.CONTRACT_PERIOD = 'Y')
SELECT COUNT(DISTINCT EMP_NUM) FROM CTE WHERE EMP_ENTERED_DATE > VALID_DATE;

My query works fine if I hardcode a particular month.How do I convert it into a procedure in a way that when I enter year, it should return the count of records in all months that year.

Answer 1

Assuming that your key is stored as varchar2 in format YYYYMM, you can use:

CREATE FUNCTION fun(yearKey IN VARCHAR2)
    RETURN NUMBER AS
    retVal    NUMBER;
BEGIN
    WITH CTE AS
             (SELECT F.PARTITION_KEY,
                     (LAST_DAY(TO_DATE((PARTITION_KEY) || '01', 'YYYYMMDD'))) AS VALID_DATE,
                     F.EMP_NUM,
                     F.EMP_KEY,
                     D.EMP_ENTERED_DATE
                FROM F_EMP_ERROR, D_EMP D
               WHERE     F.EMP_NUM = D.EMP_NUM
                     AND F.PARTITION_KEY like yearKey || '%'
                     AND d.CONTRACT_PERIOD = 'Y')
    SELECT COUNT(DISTINCT EMP_NUM)
      INTO retVal
      FROM CTE
     WHERE EMP_ENTERED_DATE > VALID_DATE;

    RETURN retVal;
END;