CODEWITHSUNDEEP
oracle-databaseplsql
Beefstu
Beefstu

Reputation: 857

Oracle PLSQL generating absences

have some PLSQL code that generates a list of dates from a range, which seems to be working fine.

As part of a larger project I want to generate a procedure that will create a list of absences for each employee.

My first step is to use the MINUS command to remove all the holidays, which fall into the range of dates. Is there an easy way of doing this instead of comparing each holiday one at a time (there maybe several in the table) against the GENERATED dates.

If possible, I would prefer breaking all these tasks into small procedures or functions for easy debugging and legibility.

If there is an easier way to do this I am open to all suggestions. Thanks in advance for your help, expertise and patience.

  ALTER SESSION SET 
       NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


    create table holidays(
      holiday_date DATE,
     holiday_name VARCHAR2(20)
    );

    INSERT into holidays
      (holiday_date,
       holiday_name)
     VALUES
    (
     TO_DATE('2021/07/21 00:00:00',   'yyyy/mm/dd hh24:mi:ss'), 'July 21 2021');


    CREATE OR REPLACE PROCEDURE generate_dates
     (
      p_start_date  IN DATE,
     p_end_date   IN DATE
    )
    AS 
     l_day        DATE := p_start_date;
   BEGIN
    WHILE l_day <= p_end_date
    LOOP              
      DBMS_OUTPUT.PUT_LINE(l_day);
       l_day := l_day + 1; 
      END LOOP;    
    END generate_dates;


     EXEC generate_dates(TRUNC(SYSDATE),TRUNC(SYSDATE+10));


     
    Create table employees(
     employee_id NUMBER(6), 
     first_name VARCHAR2(20),
     last_name VARCHAR2(20),
     card_num VARCHAR2(10),
work_days VARCHAR2(7)
    );


     ALTER TABLE employees
             ADD ( CONSTRAINT employees_pk
           PRIMARY KEY (employee_id));


    INSERT INTO employees                   
    (
    EMPLOYEE_ID,
    first_name, 
    last_name,
    card_num, 
    work_days
   )
    WITH names AS ( 
SELECT 1, 'Jane',     'Doe',      'F123456', 'NYYYYYN'   FROM dual UNION ALL 
 SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN'
     FROM dual UNION ALL 
     SELECT 3, 'Justin',   'Case',     'C765341','NYYYYYN'
     FROM dual UNION ALL 
    SELECT 4, 'Mike',     'Jones',      'D564311','NYYYYYN' FROM dual 
    ) SELECT * FROM names  

    -- check to see if working for that day. Byte=Y for Yes
  
    SELECT SUBSTR( work_days, to_char(TRUNC(SYSDATE), 'D'),1) Work_Day
   FROM employees
         
    create table timeoff(
         seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    employee_id NUMBER(6),
    timeoff_date DATE,
    timeoff_type VARCHAR2(1),
     constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),
      constraint timeoff_pk primary key (employee_id, timeoff_date)
     );


   INSERT INTO timeoff (EMPLOYEE_ID,TIMEOFF_DATE,TIMEOFF_TYPE
    ) 
     WITH dts AS ( 
        SELECT 1, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual UNION ALL 
    SELECT 2, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual UNION ALL 
    SELECT 2, to_date('20210727 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual  ) 
    SELECT * FROM dts


            CREATE TABLE  emp_attendance(    
     seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
      employee_id NUMBER(6),
      start_date DATE,
      end_date DATE,
      week_number NUMBER(2),
      create_date DATE DEFAULT SYSDATE
       );

    create table absences(
    seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    employee_id NUMBER(6),
    absent_date DATE,
    constraint absence_chk check (absent_date=trunc(absent_date, 'dd')),
      constraint absence_pk primary key (employee_id, absent_date)
    );


    INSERT INTO emp_attendance (    EMPLOYEE_ID, START_DATE,END_DATE,WEEK_NUMBER)
    WITH dts AS ( 
    SELECT 1, to_date('20210728 13:10:00','YYYYMMDD HH24:MI:SS'),
    to_date('20210728 23:15:00','YYYYMMDD HH24:MI:SS'), 30  FROM dual UNION ALL 
    SELECT 2, to_date('20210728 12:10:10','YYYYMMDD HH24:MI:SS'),
    to_date('20210728 20:15:01','YYYYMMDD HH24:MI:SS'), 30  FROM dual)
   SELECT * FROM dts



CREATE OR REPLACE TYPE obj_date IS OBJECT (
 date_val DATE

);

  CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;


  CREATE OR REPLACE FUNCTION generate_dates(
 p_from IN DATE
,p_to IN DATE)
RETURN nt_date PIPELINED

IS -- normalize inputs to be as-of midnight v_from DATE := TRUNC(NVL(p_from, SYSDATE)); v_to DATE := TRUNC(NVL(p_to, SYSDATE)); BEGIN LOOP EXIT WHEN v_from > v_to; PIPE ROW (obj_date(v_from)); v_from := v_from + 1; -- next. calendar day END LOOP; RETURN; END generate_dates;

INSERT INTO absences

(employee_id, absent_date) SELECT e.employee_id, c.date_val FROM employees e INNER JOIN table(generate_dates(date '2021-07-20', DATE '2021-07-31')) c PARTITION BY ( e.employee_id ) ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y') WHERE NOT EXISTS ( SELECT 1 FROM holidays h WHERE c.date_val = h.holiday_date ) AND NOT EXISTS( SELECT 1 FROM timeoff t WHERE e.employee_id = t.employee_id AND t.timeoff_date = c.date_val ) AND NOT EXISTS( SELECT 1 FROM emp_attendance ea WHERE e.employee_id = ea.employee_id AND TRUNC(ea.start_date) = c.date_val ) ORDER BY e.employee_id, c.date_val ;

Upvotes: 0

Views: 126

Answers (1)

MT0
MT0

Reputation: 167867

Don't use lots of procedures and/or a functions; just use a single query:

SELECT e.employee_id,
       c.day
FROM   employees e
       INNER JOIN (
         WITH calendar ( start_date, end_date ) AS (
           SELECT DATE '2021-07-01', DATE '2021-07-30' FROM DUAL
         UNION ALL
           SELECT start_date + 1, end_date
           FROM   calendar
           WHERE  start_date + 1 <= end_date
         )
         SELECT start_date AS day
         FROM   calendar
       ) c
       PARTITION BY ( e.employee_id )
       ON (SUBSTR(e.work_days, TRUNC(c.day) - TRUNC(c.day, 'IW') + 1, 1) = 'Y')
WHERE  NOT EXISTS (
         SELECT 1
         FROM   holidays h
         WHERE  c.day = h.holiday_date
       )
AND    NOT EXISTS(
         SELECT 1
         FROM   timeoff t
         WHERE  e.employee_id = t.employee_id
         AND    t.timeoff_date = c.day
       )
ORDER BY
       e.employee_id,
       c.day

Notes:

  • This assumes that your work_days column aligns with the ISO week; if it does not then you will need to adjust the substring.
  • Do not use TO_CHAR(date_value, 'D') as users will get different results depending on their NLS_TERRITORY session setting.

db<>fiddle here

Upvotes: 3

Related Questions