I have some code below, which is working fine that will generate date/times rows based on an interval.
My goal is to use those rows and combine them with a cartesian product of employees and locations in order to populate the access_history table.
For every date row there should be a combination of employee_id and location_id.
My question is do I need to combine the date CTE with the employee and location cartesian product in order to have all the values I need to populate the access_history table or is there a simpler way?
I would prefer to leave the date code alone so it not nested with any other information.
Below is my test CASE and a standalone CTE called 'combos', which I need to pull data from.
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)
);
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, 'John', 'Doe', 'D564311','YYYYYNN' FROM dual UNION ALL
SELECT 2, 'Justin', 'Case', 'C224311','YYYYYNN' FROM dual UNION ALL
SELECT 3, 'Mike', 'Jones', 'J288811','YYYYYNN' FROM dual UNION ALL
SELECT 4, 'Jane', 'Smith', 'S564661','YYYYYNN' FROM dual
) SELECT * FROM names;
CREATE TABLE locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,
CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'T'
END AS location_type
FROM dual
CONNECT BY level <= 10;
ALTER TABLE locations
ADD ( CONSTRAINT locations_pk
PRIMARY KEY (location_id));
create table access_history(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
card_num varchar2(10),
location_id number(4),
access_date date,
processed NUMBER(1) default 0
);
--- works fine --
WITH inputs ( value ) AS (
SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
FROM DUAL
),
date_rows ( start_date, end_date ) AS (
SELECT value,
TRUNC(value) + INTERVAL '1' DAY
FROM inputs
UNION ALL
SELECT start_date + INTERVAL '10' MINUTE,
end_date
FROM date_rows
WHERE start_date + INTERVAL '10' MINUTE < end_date
)
SELECT start_date
FROM date_rows;
-- need access to these rows to populate access_history table
with combos as
( select e.*, l.*
from employees e, locations l
)