Home » SQL & PL/SQL » SQL & PL/SQL » Help with the minus operator (RDBMS 11.2.0.4)
Help with the minus operator [message #681260] Wed, 01 July 2020 05:50
BeefStu
Messages: 208
Registered: October 2011
Senior Member
In an effort generate absence rows (empty table structure included)

I have the following setup and result set (see below)

I am trying to modify the code to subtract the rows in the timeoff table. In this case, there is only 1 row but many more can be in the table outside of the date ranges that are being processed.

This is the row that should not appear in the result set

3 07052020 00:00:00

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),
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

Previous Topic: Extract substring equal to a 7 digits number (merged)
Next Topic: Oracle query
Goto Forum:
  


Current Time: Thu Mar 28 12:39:18 CDT 2024