Evenly update records - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Evenly update records

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2014-01-13 : 16:26:33

Hello:

I have a process that I am running from a trigger. I am kind of unsure where to begin. I'll describe the process and see if you guys have any good suggestions about how to approach this.

I have an orders table and an employeelog table. The orders table contains order data and the employeelog table contains data on which employees are logged into the database.

I would like the orders on the order table to be "assigned" equally across each employee that is logged into the employeelog. So, my thought is for every UPDATE/INSERT on the employeelog table, to trigger a reassignment of orders on the order table equally across the number of employees logged in.

So for example. If I have 100 orders on the order table and 1 employee logged in, then that one employee would be assigned all orders and the order table would look something like this:

orderid employee
001 employee1
002 employee1
003 employee1
004 employee1
... employee1

If I have 10 orders and the employeelog table goes from 1 employee to 2 employees, then the data would need to look like this:

orderid employee
001 employee1
002 employee1
003 employee1
004 employee1
005 employee1
006 employee2
007 employee2
008 employee2
009 employee2
010 employee2

If the employeelog table went to 3 employees, it would look like this:
orderid employee
001 employee1
002 employee1
003 employee1
004 employee1
005 employee2
006 employee2
007 employee2
008 employee3
009 employee3
010 employee3

Let me know if anyone has any thoughts.

I can do this with simple T-SQL singular updates, but I'm going to have to loop through the orders table somehow and I'm unfamiliar with this process.

Thanks in advance!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-01-13 : 17:48:46

Presumably this is a queue system and later down the track an employee grabs an order and processes it or something.

One approach is to do something like this:

select Emp_Index,* from
(select
NTILE((select count(*) from Orders)) over (order by orderID) assigned_emp,
*
from ORDERS) orders
inner join
(
select
ROW_NUMBER() over (order by EmpID) Emp_Index,
* from Employees
) emps
on orders.assigned_emp=Emp_Index

But the whole thing seems unlike something you'd do in a trigger.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-13 : 17:49:30

One way might be to use the NTILE function:

-- Setup Sample Data
DECLARE @Employee TABLE (Employee VARCHAR(50))

INSERT @Employee VALUES
('Employee1'),
('Employee2'),
('Employee3'),
('Employee4')


DECLARE @Order TABLE (OrderID INT, Employee VARCHAR(50))

INSERT @Order (OrderID) VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

DECLARE @EmployeeCount INT;

SET @EmployeeCount = 
	(
		SELECT COUNT(*)
		FROM @Employee
	)

-- Update Table
UPDATE
	O
SET
	Employee = E.Employee
FROM
	(
		SELECT
			Employee,
			NTILE(@EmployeeCount) OVER (ORDER BY OrderID) AS RowNum
		FROM
			@Order AS A
	) AS O
INNER JOIN
	(
		SELECT 
			Employee,
			ROW_NUMBER() OVER (ORDER BY Employee) AS RowNum
		FROM 
			@Employee
	) AS E
	ON O.RowNum = e.RowNum

SELECT *
FROM @Order

--Results
OrderID	Employee
------- --------
1       Employee1
2       Employee1
3       Employee1
4       Employee2
5       Employee2
6       Employee2
7       Employee3
8       Employee3
9       Employee4
10      Employee4

benking9987
Posting Yak Master

124 Posts

Posted - 2014-01-13 : 19:00:17

Lamprey....perfect!!! This is exactly what I needed. Thanks to the both of you for responding. I had never even heard of the NTILE function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources