I have the following code, which appears to be working fine.
As you can in the last piece of SQL the date range is hard coded. Is there a way to wrap it in a procedure or function and return a cursor (passing the dates directly to the procedure or function ) then modifying my SQL to reference each day that falls between the date range.
Ie something like this without the hard coded dates
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
CREATE OR REPLACE PROCEDURE generate\_dates
(
p\_start\_date IN DATE,
p\_end\_date IN DATE
)
AS
BEGIN
END;
END generate\_dates;
EXEC generate_dates(
DATE '2021-07-01',
DATE '2021-07-31');
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 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;
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;
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