Data allocation in round robin fashion

svyadre · August 31, 2018, 6:42am

Hi Friends,

I have two tables A & B

A have details of unique employee with location, ecode & name.
B has Leads with Lead_id, Location & Lead_Number.

So now I want details in third table based on following condition.

Allocation of Table B Leads (all fields) in round robin fashion as per details given in Table A of Employee. Basic distribution will be based on Location matching. So if on particular location there is
4 Unique employee are available then they will have allocation of leads in round robin fashion of its
respected location. Number of available unique employees will be as per Table A.
There is condition of lead allocation per location will be restricted to allocation of maximum 10 or 20 leads per employee of respective location or for all location.
Final output availability in third table C with sorting order by location & ecode wise.

Please help to get me this, have tried so many options but not got exact requirement.

Regards,
Santosh

harishgg1 · September 1, 2018, 9:54am

hi

We need some help .. trying to understand what you are trying

Please provide data script and .. simple illustrations to help us understand
illustrations with data will help a lot

If you want to me to show .. how to explain using data as illustriations
I will

svyadre · September 1, 2018, 11:11am

Hi Harish,

Here is example/illustration as follows:

Table A (Employee master)

Ecode Ename Location Cluster
101 ABC Mumbai M1
102 DEF Mumbai M1
103 GHI Mumbai M1
104 JKL Mumbai M2
105 MNO Pune P1
106 PQR Pune P1
107 STU Pune P1
108 VWX Pune P2
109 YZA Nagpur N1

Table B (Leads)

Lead_id Location Cluster
1 Mumbai M1
2 Mumbai M1
3 Mumbai M1
4 Mumbai M1
5 Mumbai M1
6 Mumbai M2
7 Mumbai M2
8 Mumbai M2
9 Pune P1
10 Pune P1
11 Pune P1
12 Pune P1
13 Pune P1
14 Pune P1
15 Pune P2
16 Pune P2
17 Nagpur N1
18 Nagpur N1
19 Nagpur N1
20 Nagpur N2

Table C (Desired Output)

Lead_id Location Cluster Ecode Ename Location Cluster
1 Mumbai M1 101 ABC Mumbai M1
2 Mumbai M1 102 DEF Mumbai M1
3 Mumbai M1 103 GHI Mumbai M1
4 Mumbai M1 101 ABC Mumbai M1
5 Mumbai M1 102 DEF Mumbai M1
6 Mumbai M2 104 JKL Mumbai M2
7 Mumbai M2 104 JKL Mumbai M2
8 Mumbai M2
9 Pune P1 105 MNO Pune P1
10 Pune P1 106 PQR Pune P1
11 Pune P1 107 STU Pune P1
12 Pune P1 105 MNO Pune P1
13 Pune P1 106 PQR Pune P1
14 Pune P1 107 STU Pune P1
15 Pune P2 108 VWZ pune P1
16 Pune P2 108 VWZ pune P1
17 Nagpur N1 109 YZA Nagpur N1
18 Nagpur N1 109 YZA Nagpur N1
19 Nagpur N1 109 YZA Nagpur N1
20 Nagpur N2 109 YZA Nagpur N1

Details of Logic

As per base file of employee master ecode & its details has allocated the
respective leads & its details as per match of location

There is cap of 2 leads per employee of its respective location. There might
be option to map lead based on location & cluster both.

So in final outp at third table with allocation of leads & its respective mapped

ecode details.

There are cases where mapping is not eligible due to master details or cap limit,
such leads will also to be shown but having tak "Not Mapped".

Hope this illustration is enough to understand details.

Regards,

Santosh

svyadre · September 1, 2018, 11:13am

Hi Harish,

Adding further information.

I am using SQL where both tables will be at database. I want to create new table with given logic.

Regards,

Santosh

harishgg1 · September 1, 2018, 11:30am

hi looks like you are NEW

it will help to post data script like this

create data script

 USE harish_sample 
go 

DROP TABLE employee_master 

go 

CREATE TABLE employee_master 
  ( 
     ecode    INT, 
     ename    VARCHAR(100), 
     location VARCHAR(100), 
     cluster  VARCHAR(100) 
  ) 

go 

INSERT INTO employee_master 
SELECT 101, 
       'ABC', 
       'Mumbai', 
       'M1' 

INSERT INTO employee_master 
SELECT 102, 
       'DEF', 
       'Mumbai', 
       'M1' 

INSERT INTO employee_master 
SELECT 103, 
       'GHI', 
       'Mumbai', 
       'M1' 

INSERT INTO employee_master 
SELECT 104, 
       'JKL', 
       'Mumbai', 
       'M2' 

INSERT INTO employee_master 
SELECT 105, 
       'MNO', 
       'Pune', 
       'P1' 

INSERT INTO employee_master 
SELECT 106, 
       'PQR', 
       'Pune', 
       'P1' 

INSERT INTO employee_master 
SELECT 107, 
       'STU', 
       'Pune', 
       'P1' 

INSERT INTO employee_master 
SELECT 108, 
       'VWX', 
       'Pune', 
       'P2' 

INSERT INTO employee_master 
SELECT 109, 
       'YZA', 
       'Nagpur', 
       'N1' 

SELECT * 
FROM   employee_master 

go 

DROP TABLE leads 

go 

CREATE TABLE leads 
  ( 
     lead_id  INT, 
     location VARCHAR(100), 
     cluster  VARCHAR(100) 
  ) 

go 

INSERT INTO leads 
SELECT '1', 
       'Mumbai', 
       'M1' 

INSERT INTO leads 
SELECT '2', 
       'Mumbai', 
       'M1' 

INSERT INTO leads 
SELECT '3', 
       'Mumbai', 
       'M1' 

INSERT INTO leads 
SELECT '4', 
       'Mumbai', 
       'M1' 

INSERT INTO leads 
SELECT '5', 
       'Mumbai', 
       'M1' 

INSERT INTO leads 
SELECT '6', 
       'Mumbai', 
       'M2' 

INSERT INTO leads 
SELECT '7', 
       'Mumbai', 
       'M2' 

INSERT INTO leads 
SELECT '8', 
       'Mumbai', 
       'M2' 

INSERT INTO leads 
SELECT '9', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '10', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '11', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '12', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '13', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '14', 
       'Pune', 
       'P1' 

INSERT INTO leads 
SELECT '15', 
       'Pune', 
       'P2	' 

INSERT INTO leads 
SELECT '16', 
       'Pune', 
       'P2' 

INSERT INTO leads 
SELECT '17', 
       'Nagpur', 
       'N1' 

INSERT INTO leads 
SELECT '18', 
       'Nagpur', 
       'N1' 

INSERT INTO leads 
SELECT '19', 
       'Nagpur', 
       'N1' 

INSERT INTO leads 
SELECT '20', 
       'Nagpur', 
       'N2' 

go 

SELECT * 
FROM   leads 

go

harishgg1 · September 1, 2018, 12:15pm

Hi

Mapping on
only location

My Query

you said
There might
be option to map lead based on location & cluster both
( what does this mean ? do we join on location only or join on location and cluster both )

SELECT abc.*
FROM (SELECT Row_number()
OVER(
partition BY a.ecode
ORDER BY b.lead_id) AS rownum,
a.ecode,
a.ename,
a.location,
a.cluster,
b.lead_id
FROM employee_master a
JOIN leads b
ON a.location = b.location) abc
WHERE abc.rownum IN ( 1, 2 )

harishgg1 · September 1, 2018, 12:17pm

My result set .. does this look like what you want

svyadre · September 1, 2018, 12:49pm

Hi Harish,

Currently on location, if possible with location and cluster would be great help to me.

Regards,

Santosh

harishgg1 · September 1, 2018, 12:54pm

adding cluster join also QUERY

select abc.* from 
(
select 
ROW_NUMBER() over(partition by a.ecode order by b.lead_id) as rownum ,
a.ecode    ,
     a.ename ,   
     a.location, 
     a.cluster,
     b.lead_id   
from 
Employee_Master a 
join 
leads b 
on a.Location = b.location and a.Cluster = b.cluster) abc
where abc.ROWNUM in (1,2)

harishgg1 · September 1, 2018, 12:56pm

adding cluster join also .. RESULT SET

svyadre · September 1, 2018, 1:23pm

Hi Harish,

Yes, ok

Regards,

Santosh

svyadre · September 2, 2018, 5:04pm

Hi Harish,

Great work, really thanks a lot to support me & also make learned me to create data script.

You are simply great person....quick helped me !

Regards,
Santosh

svyadre · September 11, 2018, 10:14am

Hi Harish,

I really thank you again for your help.

One small thing I found out while trying this is that allocation of leads are not as per total available leads in leads table.

E.g. If there are 5 employee and total leads as per leads table are 4 then it has to distribute 4 only.

As per per employee cap of 2 leads per employee, where in maximum leads case, if per employee lead cap should not be exceeded. e.g for 5 unique employee if lead table has 22 leads then as per cap of 2 per it should allocate 20 only and rear to be as "To be allocated" tag.

So considering cap per employee allocation should be as per lead table data where minimum or maximum case.

Regards,

Santosh

femiolan · September 14, 2018, 7:23pm

Is this what you need... change table names to suite.

;with cte
as
(

SELECT a.lead_id 
,a.location 
,a.cluster 
,
(
case 
when b.cnt = 0 then 0


when a.rn > b.cnt * 2 then 0

else ((a.rn - 1) % b.cnt ) + 1 
 
end 
) [x]
 
FROM   
(
select a.*, ROW_NUMBER() over(PARTITION by a.location, a.cluster order by a.lead_id asc)   [rn]
from #leads a
) a
outer apply
	(
	select COUNT(*) [cnt]
	from #employee_master b 
	where b.location = a.location  
	and b.cluster  = a.cluster 
	) b

)

select 
a.lead_id 
,a.location
,a.cluster 
,isnull(cast(b.ecode as varchar(40)) , '')  ecode
,isnull(b.ename, '')  ename
,isnull(b.location , '') location
,isnull(b.cluster , '' ) cluster

from cte a
left join 
	(
	select a.* , ROW_NUMBER() over(PARTITION by a.Location, a.Cluster order by a.ecode asc) [x] 
	from #employee_master a
	) b
on a.location = b.location  and a.cluster = b.cluster  and a.x = b.x


order by 1;