Database
Enthusiast

## Getting a dynamic Sample

I am attempting  to create a population based on percentages stored in a table. The table structure is -

`employee_id dept allocation_percentage1 english  1002 math   39.332592 science   60.667413 math  1004 science  1005 science  1006 science   41.289756 history   58.710257 english   69.461177 math   30.538838 english   69.461178 math   30.53883`

I want to write a sql which can select a random sample of employees distributed as per following logic -

1.First identify all the employees that share the same departments. Like 7 and 8 in above example. BOth have math and english.

2. For each of these groups randomly distribute them as per allocation_percentage into different departments. So out of all  the employees who took english and math 69.46 % should be assigned to english and 30.53 to math.

Any help is greatly appreciated. Thank you so much in advance.

Regards,

San

6 REPLIES
Junior Contributor

## Re: Getting a dynamic Sample

Hi San,

some questions:

Are the shared departments known in advance or should the query find them?

Are there multiple combinations?

Can there be more than two shared departments?

Is the allocation_percentage the same for all employee sharing the same departments?

Do the percentages always add up to 100?

How many rows are in that table?

And finally, what are you actually trying to do? :-)

Enthusiast

## Re: Getting a dynamic Sample

So basically each department is claiming that they should be responsible for the employee since the employee works for more than one department. So I want to assign each employee to one department each. Now there can be many combinations of the departments.

There can be more than 2 shared departments. The percentages always add up to 100.

The allocation % for the same combination will always remain same.

So the way I was trying to do is -

1. I created a column concatenating all the departments for one employee.

2. Then I am doing a group by on that concatenated column.

3. My problem is - I am unable to allocate employees as per the percentages. Like  out of all  the employees who took english and math 69.46 % should be assigned to english and 30.53 to math from above example.

Junior Contributor

## Re: Getting a dynamic Sample

You can assign a percentage based on a PERCENT_RANK * 100. If it needs to be statistically random you must use a Derived Table to create a RANDOM(1,2000000000) number to ORDER BY it within the PERCENT_RANK.

Is this related to your other post on recursion?

Enthusiast

## Re: Getting a dynamic Sample

I was able to get the recursive things working from other post - to get a column with concatenated departments.

I was trying something like this to get the data -

WITH RECURSIVE rec_emps(emp_id, dept_id, ALL_depts)

AS   (

select distinct emp_id, dept_id, ALL_depts from vt_alloc3 qualify row_number() OVER (PARTITION BY all_depts order by  emp_id) = 1

union all

(

select  a.* from  (

select distinct vt_alloc3.emp_id, vt_alloc3.dept_id, vt_alloc3.ALL_dept from vt_alloc3 WHERE ALLOC_PCT <> 100

qualify row_number() OVER (PARTITION BY vt_alloc3.ALL_dept, vt_alloc3.dept_id order by  vt_alloc3.emp_id) <= (( vt_alloc3.alloc_pct * vt_alloc3.cnt_cust ) /100)

) a  where

vt_alloc3.emp_id <> a.emp_id

)

)

SELECT * from rec_emps order by  emp_id,all_dept

Enthusiast

## Re: Getting a dynamic Sample

my cnt_cust is the total number of customers per each combination of departments.

Enthusiast

## Re: Getting a dynamic Sample

Also, can you please send me a sample query on the approach you suggested? Thank you so much!!