How to Divide Customer for Assignment in SQL Server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi I Want to Divide certain Customer(Ex 100) among 2 Employees having Count(10) set in Table.
so i want the result as
10 CUstomer should be assiged to 1 Employee
and other 10 Customer should be assign to 2 Employee

and rest of the 80 Customer should be assinged equally of (20) each day

next day 20
next day 20
....

thanks

Posted 26-Apr-15 21:20pm

Sachin MCA2012

Add a Solution

Comments

Deepu S Nair 27-Apr-15 3:28am

So what you tried so far?

deepankarbhatnagar 27-Apr-15 3:47am

Your code please

Tomas Takac 27-Apr-15 4:03am

This sounds more like an exercise. In real-life application you would rather assign the customers on the fly on a FIFO basis. Assigning customers to employees for days in advance is not very flexible.

Sachin MCA2012 27-Apr-15 4:46am

select ClientCode,1 as ProductID from @ProcessTable where BranchCode=@BranchCode
)Process1Close
left outer join
( select * from
(
select ROW_NUMBER() over(order by AlreadyAssignedCount) as RN,HRMSEMP.HRMSEmpCode as EMPCode,HRMSEMP. AlreadyAssignedCount 'AlreadyAssignedCount' from
( select EMP.*,isnull(EachEmpCount.TotalCnt,0) as AlreadyAssignedCount from @HRMSEMP EMP
left outer join
(select EmpID,count (ClientID)TotalCnt from CRMN_Project_Assign where ProductId =1 and ProjectID=@ProcessID
group by EmpID
)EachEmpCount on EMP.HRMSEmpCode = EachEmpCount.EmpID
)HRMSEMP
)EMP --where RN=@b
)EMP1 on
EMP1.RN =
(main.RN - 1) %
(
select count(RN) from @HRMSEMP
) + 1 where EMP1.EMPCode is not null
order by main.RN

Tomas Takac 27-Apr-15 5:25am

Comments are not the right place to post code. Use "Improve question" button to edit your question and put the code there. Don't forget to properly format it so people can easily read it. Then delete these comments to keep the discussion clean. Cheers.

Sachin MCA2012 27-Apr-15 4:59am

select dbo.encryptbyphrase(main.ClientCode),main.ProductID,main.ProjectID,Emp1.EmpCode,getdate() from
(
select ROW_NUMBER() OVER(ORDER BY ClientCode asc) AS 'RN',ClientCode,1 as ProductID,@ProcessID as ProjectID from
(
select ClientCode,1 as ProductID from @ProcessTable where BranchCode=@BranchCode
)Process1Close
left outer join (select dbo.decryptbyphrase(ClientID) 'ClientID' from CRMN_Project_Assign where Productid =1 and ProjectID=@ProcessID ) CC
on CC.ClientID= Process1Close.ClientCode where isnull(CC.ClientID ,'')=''

)main
left outer join
( select * from
(
select ROW_NUMBER() over(order by AlreadyAssignedCount) as RN,HRMSEMP.HRMSEmpCode as EMPCode,HRMSEMP. AlreadyAssignedCount 'AlreadyAssignedCount' from
( select EMP.*,isnull(EachEmpCount.TotalCnt,0) as AlreadyAssignedCount from @HRMSEMP EMP
left outer join
(select EmpID,count (ClientID)TotalCnt from CRMN_Project_Assign where ProductId =1 and ProjectID=@ProcessID
group by EmpID
)EachEmpCount on EMP.HRMSEmpCode = EachEmpCount.EmpID
)HRMSEMP
)EMP --where RN=@b
)EMP1 on
EMP1.RN =
(main.RN - 1) %
(
select count(RN) from @HRMSEMP
) + 1 where EMP1.EMPCode is not null
order by main.RN

END

This is the Code @HRMSEMP which contain Employee
@ProcessTable this table contain CLient Code

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)