I have some working SQL below that generates a row for each employee_id.
I could use some SQL expertise modifying my SQL code to call the function, generate a range of dates, for each date generate N (1-10) rows for each employee_id. I want to remove the call to dbms_random.value (0, 2) + dbms_random.value (0, .75) and replace it with the value returned from the generate_dates_pipelined() FUNCTION. Once this works I intend to put this code in a procedure so I can pass it a range of dates.
Below is some sample output for a single day.
EMPLOYEE_ID CARD_NUM LOCATION_ID ACCESS_DATE
1 F123456 10 07302021 09:47:48
1 F123456 5 07282021 19:17:42
2 R33432 1 4 07282021 02:00:37
3 C765341 2 07282021 17:33:57
3 C765341 6 07282021 17:33:57
3 C765341 1 07282021 18:53:07
4 D564311 6 07282021 03:06:37
ALTER SESSION SET
NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
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 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));
SELECT
e_id AS employee_id,
card_num,
l_id AS location_id,
trunc(sysdate) + dbms_random.value (0, 2) + dbms_random.value (0, .75) AS access_date
FROM (
SELECT
employee_id AS e_id,
row_number() OVER (ORDER BY dbms_random.value) AS rn, card_num
FROM employees
) x
JOIN (SELECT
location_id AS l_id,
row_number() OVER (ORDER BY dbms_random.value) AS rn
FROM locations) y USING (rn)
ORDER by employee_id, location_id;
Aucun commentaire:
Enregistrer un commentaire