mardi 23 juin 2020

Oracle creating random count of rows per employee

I have the following INSERT statement, which works fine.

I also found some code on the internet that creates a random count of employee records, which works fine.

Can someone provide a solution that merges the random count code into the INSERT statement. Thanks to all who respond.

create table access_history(
   employee_id NUMBER(6), 
   card_num varchar2(10),
   location_id number(4),
   access_date date,
   processed NUMBER(1) default 0
);

INSERT into access_history
 (employee_id,
   card_num,
  location_id,
   access_date)
   with all_combos as
      ( select e.*, l.*
      from   employees e, locations l
     )
    select *
     from (
       select employee_id, card_num,
               location_id, 
             trunc(sysdate) +     dbms_random.value (0, 2) + dbms_random.value (0, .75)
        from   all_combos
        order by dbms_random.value
  );

with
 emps as
    ( select level empid, dbms_random.value(5,20) children from dual connect by level <= 20 ),
  empatt as
    ( select e.empid , x.start_date, x.start_date+dbms_random.value(0,0.75) end_date
      from emps e,
           lateral(
           select
               trunc(sysdate)+dbms_random.value(1,30) start_date
          from dual
           connect by level <= e.children
          ) x
    )

select empid, count(*) from empatt group by empid order by 1;




Aucun commentaire:

Enregistrer un commentaire