mercredi 28 juillet 2021

Creating a random numbers for a range if dates

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