Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update table upon condition

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 following

Call table

ID 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-XXXX

My 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-XXXX


Any 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.

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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...
Go to Top of Page

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?
Go to Top of Page

matrixrep
Starting Member

30 Posts

Posted - 2013-02-01 : 14:16:45
I have two table: - caller
- telephone

Here is the script:

DECLARE @id AS INT
DECLARE @MIN nvarchar(12)
DECLARE @ENS nvarchar(50)

-- here is the loop until all calls are assigned

WHILE 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=@ID


END




If you have a more elegant way to dot it, any suggestion is welcome.
This solution is actually working well.




Go to Top of Page
   

- Advertisement -