SQL Server Query help: I have 10 employees and real time data is coming, while I need to Insert each records equally distribution.
Example:
Data=1 then EmployeeId=1, Next time Data=2 should be Insert to EmployeeId=2 and this cycle will continue as per received raw data.
USE tempdb;
GO
/* Just setting up some tables to test with... */
CREATE TABLE dbo.Employee (
EmployeeID INT NOT NULL
CONSTRAINT pk_Employee
PRIMARY KEY CLUSTERED,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
DepartmentID TINYINT NOT NULL,
PrimaryJobTitle VARCHAR(20) NOT NULL
);
GO
/* Note: I'm assuming that only a certain subset of employees will be assigned "data".
In this case, those employees are in department 4, with a primary jobt itle of "Do Stuff"... */
INSERT dbo.Employee (EmployeeID, FirstName, LastName, DepartmentID, PrimaryJobTitle) VALUES
( 1, 'Jane', 'Doe', 1, 'CEO'),
( 2, 'Alex', 'Doe', 2, 'CIO'),
( 3, 'Bart', 'Doe', 3, 'CFO'),
( 4, 'Cami', 'Doe', 4, 'COO'),
( 5, 'Dolt', 'Doe', 3, 'Accountant'),
( 6, 'Elen', 'Doe', 4, 'Production Manager'),
( 7, 'Flip', 'Doe', 4, 'Do Stuff'),
( 8, 'Gary', 'Doe', 4, 'Do Stuff'),
( 9, 'Hary', 'Doe', 2, 'App Dev'),
(10, 'Jill', 'Doe', 4, 'Do Stuff'),
(11, 'Kent', 'Doe', 4, 'Do Stuff'),
(12, 'Lary', 'Doe', 4, 'Do Stuff'),
(13, 'Many', 'Doe', 4, 'Do Stuff'),
(14, 'Norm', 'Doe', 4, 'Do Stuff'),
(15, 'Paul', 'Doe', 4, 'Do Stuff'),
(16, 'Qint', 'Doe', 3, 'Accountant'),
(17, 'Ralf', 'Doe', 4, 'Do Stuff'),
(18, 'Saul', 'Doe', 4, 'Do Stuff'),
(19, 'Tony', 'Doe', 4, 'Do Stuff'),
(20, 'Vinn', 'Doe', 4, 'Do Stuff');
GO
CREATE TABLE dbo.WorkAssignment (
WorkAssignmentID INT IDENTITY(1,1) NOT NULL
CONSTRAINT pk_WorkAssignment
PRIMARY KEY CLUSTERED,
WorkOrder INT NOT NULL,
AssignedTo INT NOT NULL
CONSTRAINT fk_WorkAssignment_AssignedTo
FOREIGN KEY REFERENCES dbo.Employee(EmployeeID)
);
GO
--===================================================================
/* This is where the actual solution begins... */
/*
Blindly assigning work orders in “round-robin” order is pretty simple but probably not applicable to the real world.
It seems unlikely that all employees who will be assigned work will all have their EmployeeIDs assigned 1 - N without any gaps…
If new work assignments were to start at 1 every time, the employees with low number IDs would end up being assigned more work
than those with the highest IDs… and… if “assignment batches” tend to be smaller than the employee count, employees with
high ID numbers may never get any work assigned to them.
This solution deals with both potential problems by putting the “assignable” employees into a #EmployAssignmentOrder table where
the AssignmentOrder guarantees a clean unbroken sequence of numbers, no matter the actual EmployeeID values, and picks up
where the last assignment left off.
*/
IF OBJECT_ID('tempdb..#EmployAssignmentOrder', 'U') IS NOT NULL
DROP TABLE #EmployAssignmentOrder;
CREATE TABLE #EmployAssignmentOrder (
EmployeeID INT NOT NULL,
AssignmentOrder INT NOT NULL
);
DECLARE
@LastAssignedTo INT = ISNULL((SELECT TOP (1) wa.AssignedTo FROM dbo.WorkAssignment wa ORDER BY wa.WorkAssignmentID DESC), 0),
@AssignableEmpCount INT = 0;
INSERT #EmployAssignmentOrder (EmployeeID, AssignmentOrder)
SELECT
e.EmployeeID,
AssignmentOrder = ROW_NUMBER() OVER (ORDER BY CASE WHEN e.EmployeeID <= @LastAssignedTo THEN e.EmployeeID * 1000 ELSE e.EmployeeID END )
FROM
dbo.Employee e
WHERE
e.DepartmentID = 4
AND e.PrimaryJobTitle = 'Do Stuff';
SET @AssignableEmpCount = @@ROWCOUNT;
ALTER TABLE #EmployAssignmentOrder ADD PRIMARY KEY CLUSTERED (AssignmentOrder);
/* Using an "inline tally" to generate new work orders...
This won’t be part of you final working solution BUT you should recognize the fact that we are relying on the the fact that the
ROW_NUMBER() function is generating an ordered number sequence, 1 - N...You’ll need to generate a similar sequence in your
production solution as well.
*/
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (1999)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
)
INSERT dbo.WorkAssignment (WorkOrder, AssignedTo)
SELECT
WorkOrder = t.n + DATEDIFF(SECOND, '20180101', GETDATE()),
eao.EmployeeID
FROM
cte_Tally t
JOIN #EmployAssignmentOrder eao
ON ISNULL(NULLIF(t.n % @AssignableEmpCount, 0), @AssignableEmpCount) = eao.AssignmentOrder;
-- Check the newly inserted values...
SELECT
*
FROM
dbo.WorkAssignment wa
ORDER BY
wa.WorkAssignmentID;
--===================================================================
-- cleanup...
/*
DROP TABLE dbo.WorkAssignment;
DROP TABLE dbo.Employee;
DROP TABLE #EmployAssignmentOrder;
*/
