I found some code on the internet that generates virtual dates and fit it to my table layout and works fine.
https://searchoracle.techtarget.com/answer/Creating-a-virtual-SQL-table-for-date-value
After the range of dates have been created I want to exclude all dates in the holidays table if they fall into the range of dates, which haves been generated.
I tried using the MINUS function, I think that was the right idea, but was unsuccessful.
Can someone please explain how this can be done based on my test case.
I know there is much more functionality that needs to be added like employee vacation days 1 for each day or a range,scheduled days off for a week (not always sat, sun for all)… but that is way too overwhelming at this point as I want to start with smaller tasks.
Thanks to all who answer.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
Create table employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
card_num VARCHAR2(10)
);
INSERT into employees(
employee_id, first_name,
last_name, card_num)
VALUES
(1, 'John', 'Doe', 'AAA1');
INSERT into employees(
employee_id, first_name,
last_name, card_num)
VALUES
(2, 'Jane', 'Smith', 'BBB2');
INSERT into employees(
employee_id, first_name,
last_name, card_num)
VALUES
(3, 'Ed', 'Jones', 'CCC3');
CREATE TABLE emp_attendance (seq_num NUMBER(10),
employee_id NUMBER(6),
start_date DATE,
end_date DATE, create_date DATE DEFAULT SYSDATE );
INSERT into emp_attendance (seq_num, employee_id, start_date, end_date)
VALUES
(1, 1,
TO_DATE('2020/07/03 15:15:04', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2020/07/03 19:17:34', 'yyyy/mm/dd hh24:mi:ss'));
INSERT into emp_attendance (seq_num, employee_id, start_date, end_date)
VALUES
(2,2,
TO_DATE('2020/07/03 08:16:34', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2020/07/03 11:11:45', 'yyyy/mm/dd hh24:mi:ss'));
INSERT into emp_attendance (seq_num, employee_id, start_date, end_date)
VALUES
(3,2,
TO_DATE('2020/07/03 18:17:04', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2020/07/03 21:18:54', 'yyyy/mm/dd hh24:mi:ss'));
INSERT into emp_attendance (seq_num, employee_id, start_date, end_date)
VALUES
(4,3,
TO_DATE('2020/07/04 08:15:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2020/07/04 16:19:04', 'yyyy/mm/dd hh24:mi:ss'));
create table holidays(
holiday_date DATE,
holiday_name VARCHAR2(20)
);
INSERT into holidays
(holiday_date,
holiday_name)
VALUES
(
TO_DATE('2020/07/04 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
'July 4th 2020');
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;
/
-- should be inserted into an absence table.
SELECT e.employee_id, gd.date_val
FROM employees e
CROSS JOIN
TABLE(generate_dates(DATE '2020-07-02', DATE '2020-07-05')) gd
MINUS
SELECT e.employee_id, trunc(ea.start_date)
FROM employees e
INNER JOIN emp_attendance ea
ON ea.employee_id = e.employee_id
WHERE ea.start_date BETWEEN DATE '2020-07-02' AND DATE '2020-07-05' + (1-1/24/60/60)
ORDER BY 1, 2;