Skip to Main Content
  • Questions
  • Generating a random number of rows for every date within a date range

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Paul.

Asked: July 29, 2021 - 7:00 am UTC

Last updated: July 30, 2021 - 11:01 am UTC

Version: 19c

Viewed 100+ times

You Asked

I have some working SQL below that generates a row for each employee_id.

My goal is to get every date in the range via the function, which works fine standalone, then get N ( random number ( 1-10) of rows for each employee_id for every in the range specified.

Once the SQLworks I intend to put this code in a procedure so I can pass it a range of dates. So we can assure we are both running the same version of Oracle I tested this on live SQL.

Below is some sample output for a single day only. Please note the employee_id and location_id must exist in their corresponding tables.

Since my function call always generates dates with a time of 00:00:00 I plan on eventually adding time to the access_date.

EMPLOYEE_ID    CARD_NUM LOCATION_ID    ACCESS_DATE
          1    F123456           10    07302021 09:47:48
          1    F123456            5    07282021 19:17:42
                  
          2    R33432             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 'G' 
         END AS location_type
  FROM   dual
 CONNECT BY level <= 10;


ALTER TABLE locations 
     ADD ( CONSTRAINT locations_pk
          PRIMARY KEY (location_id));

SELECT e.employee_id,
     e.card_num,
     l.location_id,   
     c.access_date,
     e.rn, l.rn, c.rn
FROM   ( 
 SELECT employee_id,
        round ( dbms_random.value ( 1, 10 ) ) rn,
        card_num
 FROM   employees
) e
INNER JOIN
( SELECT location_id, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM locations
) l
ON (e.rn = l.rn)
INNER JOIN
( SELECT COLUMN_VALUE AS access_date, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM   TABLE(generate_dates_pipelined(SYSDATE, ADD_MONTHS(SYSDATE, 1)))
) c
ON (e.rn >= c.rn)
ORDER BY
     employee_id,
     location_id;

and we said...

You're close.

Instead of generating a random row number for each employee, just generate a random number from 1-10. Then join all the dates with a (random) row number less than or equal to this value.

If you know the locations are numbered 1-10 with no gaps, you could use this way to generate these IDs in the top select too.

If not, you can use cross apply to "join" this table to the dates too, returning the first of these.

SELECT e.employee_id,
     e.card_num,
     l.location_id,   
     c.access_date
FROM   ( 
 SELECT employee_id,
        round ( dbms_random.value ( 1, 10 ) ) rn,
        card_num
 FROM   employees
) e
INNER JOIN
( SELECT COLUMN_VALUE AS access_date, 
        row_number() OVER  (ORDER BY dbms_random.value) AS rn
 FROM   TABLE(generate_dates_pipelined(SYSDATE, ADD_MONTHS(SYSDATE, 1)))
) c
ON (e.rn >= c.rn)
CROSS APPLY ( 
SELECT * FROM (
    SELECT location_id, 
           row_number() OVER  (ORDER BY dbms_random.value) AS rn
    FROM   locations
) l 
WHERE l.rn <= c.rn
FETCH FIRST 1 ROWS ONLY
) l
ORDER BY
     employee_id,
     location_id;
     
EMPLOYEE_ID CARD_NUM     LOCATION_ID ACCESS_DATE         
          1 F123456                2 07292021 00:00:00    
          1 F123456                2 08122021 00:00:00    
          1 F123456                4 08012021 00:00:00    
          1 F123456                6 08032021 00:00:00    
          1 F123456               10 08102021 00:00:00    
          2 R33432                 2 08012021 00:00:00    
          2 R33432                 5 08102021 00:00:00    
          2 R33432                 8 08032021 00:00:00    
          3 C765341                2 08012021 00:00:00    
          3 C765341                5 08122021 00:00:00    
          3 C765341                7 07292021 00:00:00    
          3 C765341                7 08102021 00:00:00    
          3 C765341                9 08032021 00:00:00    
          4 D564311                1 08122021 00:00:00    
          4 D564311                1 08012021 00:00:00    
          4 D564311                2 08032021 00:00:00    
          4 D564311                3 08102021 00:00:00 

Rating

  (1 rating)

Comments

Paul, July 29, 2021 - 11:15 am UTC

Your solution is almost perfect. When I changed to ORDER BY
access_date,
employee_id,
location_id;

I can see missing employee_id for some dates. I'm looking for every date, every employee_id and N number of rows. Can you please provide an updated solution. Apologies for any misunderstanding. Thanks for your prompt response

Chris Saxon
July 30, 2021 - 11:01 am UTC

What exactly is it you're looking for? What have you tried? I'm sure you can figure it out from here :)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library