Author |
Topic |
matrixrep
Starting Member
30 Posts |
Posted - 2013-01-29 : 16:06:42
|
I have 2 callers ( A and B ) that i am assigning phone calls to make each night, i have to assign the same number of call.In a table i have the followingCall tableID Caller PhoneNumber Done 1 A XXX-XXX-XXXX yes 2 A XXX-XXX-XXXX 3 A XXX-XXX-XXXX 4 B XXX-XXX-XXXX yes 5 B XXX-XXX-XXXX yes 6 B XXX-XXX-XXXX The next day i will add 3 more calls to make. And update the table LIKE the following.ID Caller Phone Number Done 1 A XXX-XXX-XXXX yes 2 A XXX-XXX-XXXX 3 A XXX-XXX-XXXX 4 B XXX-XXX-XXXX yes 5 B XXX-XXX-XXXX yes 6 B XXX-XXX-XXXX 7 XXX-XXX-XXXX 8 XXX-XXX-XXXX 9 XXX-XXX-XXXXMy problem is: how to assign the same number of calls per caller in a list while updating this table so each will have a list of 3 calls? Like this:ID Caller Phone Number Done 1 A XXX-XXX-XXXX yes 2 A XXX-XXX-XXXX 3 A XXX-XXX-XXXX 4 B XXX-XXX-XXXX yes 5 B XXX-XXX-XXXX yes 6 B XXX-XXX-XXXX 7 A XXX-XXX-XXXX 8 B XXX-XXX-XXXX 9 B XXX-XXX-XXXXAny help is welcome. |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-01-29 : 21:37:22
|
check out the NTILE function. Split the set of data (calls not done or unasigned calls or however you want to do it - your data is not clear) then you will have a tile (or bucket if you prefer). You can then select your callers and assign a number (using ROW_NUMBER) to them and join to the tiles. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 23:40:59
|
the output still doesnt make calls equal to the callers right------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
matrixrep
Starting Member
30 Posts |
Posted - 2013-01-30 : 08:50:13
|
in the second table i will insert the new calls to make.After inserting the new call to make, i need an update script to make sure that every caller has the same number of call to make the next day.My problem is how to script an update that will assign the same amount of call per caller everyday like in the third table. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-30 : 12:05:51
|
Isn't it just math?Get the number of open calls for A and B. Get the number of unassigned calls. Take the difference between A & B. Assign that number (difference) of calls to the Caller with the least open calls. Then if there is any remaining unassigned calls divid by 2 and divy them out to each caller, perhaps adding some logic for any left over calls. |
|
|
matrixrep
Starting Member
30 Posts |
Posted - 2013-01-30 : 15:44:00
|
In reality i have a lot more than 2 caller and they make a lot more than 3 calls per day.I can always do the math for 2. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-01-31 : 08:43:08
|
ntile deals with that but you might have to count first then do the sql work dynamically - I can't remember if ntile accepts variables.but basically you need to do what I said even if you do a count(*) calls/count(*) callers or somerthing first then a row_number. Your question and example data don't make it easy enough for me to do the work for you so you'll have to do some reading and see what is appropriate.Paste some usable data and that might change... |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-31 : 11:34:30
|
It sounds like you might need some sort of "Best Fit" Algorithm. If I understand what you are trying to do then, bascially, you are going to need to get the get a set of all callers that are currently assigned calls that are not done. Then get all the calles to be assigned. Then you need to deterime now you want to distribute those calls.... Do you start with the caller with the lowest number of not done calls and assign "new" calls to them until the are equal with the call with the next greatest number of calls and so on? Or do you just assign one to everyone? |
|
|
matrixrep
Starting Member
30 Posts |
Posted - 2013-02-01 : 14:16:45
|
I have two table: - caller - telephoneHere is the script:DECLARE @id AS INTDECLARE @MIN nvarchar(12)DECLARE @ENS nvarchar(50)-- here is the loop until all calls are assignedWHILE EXISTS(SELECT TOP 1 number FROM telephone WHERE caller is Null)BEGIN SELECT TOP 1 @id=number FROM telephone WHERE caller is Null-- This section is to find the lowest number of calls still to make per caller The caller must retain his oldest call that were not completed. Set @MIN = (SELECT top 1 MIN(call) from (SELECT caller AS user, COUNT( * ) AS call FROM telephone WHERE Present is null AND Refuse is null AND caller is not NULL Group by caller Union SELECT caller.user, 0 AS call FROM caller LEFT JOIN telephone ON caller.user = telephone.caller WHERE caller.Actif='1' AND caller.user not in (SELECT caller FROM telephone WHERE Present is null AND Refuse is null AND caller is not NULL) Group BY user) o group by call order by call) -- This section find one caller with the least amount of calls to make. Set @ENS = (SELECT TOP 1 user from (SELECT caller AS user, COUNT( * ) AS call FROM telephone WHERE Present is null AND Refuse is null AND caller is not NULL Group by caller Union SELECT caller.user, 0 AS call FROM caller LEFT JOIN telephone ON caller.user = telephone.caller AND caller.Actif='1' WHERE caller.Actif='1' AND caller.user not in (SELECT caller FROM telephone WHERE Present is null AND Refuse is null AND caller is not NULL) Group BY user) o WHERE call = @MIN) -- This section assign a call to the caller UPDATE telephone SET caller=@ENS Where number=@IDENDIf you have a more elegant way to dot it, any suggestion is welcome.This solution is actually working well. |
|
|
|