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),
work_days VARCHAR2(7)
);
-- employee off sat&sun
INSERT into employees(
employee_id,
first_name,
last_name,
card_num,
work_days)
VALUES
(1, 'John', 'Doe', 'AAA1', 'NYYYYYN');
-- employee off sat&sun
INSERT into employees(
employee_id,
first_name,
last_name,
card_num,
work_days)
VALUES
(2, 'Jane', 'Smith', 'BBB2','NYYYYYN');
-- -- employee off mon&fri
INSERT into employees(
employee_id,
first_name,
last_name,
card_num,
work_days)
VALUES
(3, 'Ed', 'Jones', 'CCC3','YNYYYNY');
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');
INSERT into holidays
(holiday_date,
holiday_name)
VALUES
(
TO_DATE('2020/12/25 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
'Christmas 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;
/
create table timeoff(
seq_num NUMBER,
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)
);
create sequence timeoff_seq;
-- vacation day
INSERT into timeoff
( seq_num,
employee_id,
timeoff_date,
timeoff_type
)
VALUES
(timeoff_seq.NEXTVAL,
3,
TO_DATE('2020/07/05 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'V');
create table absences (
seq_num NUMBER,
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)
);
create sequence absence_seq;
WITH date_range AS
( -- remove hard code
SELECT DATE '2020-07-02' AS start_date
, DATE '2020-07-05' AS end_date
FROM dual
)
, dates_wanted AS
(
SELECT g.date_val
FROM date_range r1
CROSS APPLY (TABLE (generate_dates(r1.start_date, r1.end_date))) g
MINUS
SELECT holiday_date
FROM holidays h
JOIN date_range r2 ON h.holIday_date
BETWEEN r2.start_date AND r2.end_date
-- get holidays in range and remove them
)
SELECT ea.employee_id, da.date_val
FROM dates_wanted da -- a is for "All"
CROSS JOIN employees ea
MINUS
SELECT ep.employee_id, dp.date_val
FROM dates_wanted dp -- p is for "Present"
INNER JOIN emp_attendance ep ON ep.start_date BETWEEN dp.date_val
-- now there will be a row in the result set for every combination of rows from dp and ep that meet the join conditions.
AND dp.date_val + 1 - INTERVAL '1' SECOND
ORDER BY employee_id, date_val
;
EMPLOYEE_ID DATE_VAL
1 07022020 00:00:00
1 07052020 00:00:00
2 07022020 00:00:00
2 07052020 00:00:00
3 07022020 00:00:00
3 07032020 00:00:00
3 07052020 00:00:00