SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Emp ([ID] varchar(2)) ; INSERT INTO Emp ([ID]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ; 

CREATE TABLE Center ([ID] int, [EmpQty] int) ; INSERT INTO Center ([ID], [EmpQty]) VALUES (01, 2), (02, 3), (03, 2) ; 

CREATE TABLE Sch ([Datetime] datetime, [CenterID] int, [EmpID] varchar(2)) ;

7 rows affected

CREATE PROCEDURE SP_SCH_Center_ByEmp   
    @v_date datetime  
AS  

declare @v_firstEmp nvarchar(2) =  (
	--找出昨天誰排班最少，藉由group + rownumber count,接著篩選出rank = 1的資料
	select [EmpID] 
	from (
		select [EmpID]
			,row_number() over (order by count(1),[EmpID]) rnk 
		from Sch
		where [Datetime] = (@v_date-1)
		group by [EmpID]
	) T where rnk = 1 
);
declare @v_empCount int = (select count(1) from Emp);

--如果@v_firstEmp沒資料，以第一個員工為準
--如果@v_firstEmp有資料，以前天排最少班的第一個員工為準
if @v_firstEmp is null begin
	select @v_firstEmp = (select top 1 [ID] from Emp);
end;

--先把當天Center資料展出來(使用遞迴)
WITH CTE AS (
	SELECT 1 as Number,ID,EmpQty from Center
	UNION ALL
	SELECT Number+1,ID,EmpQty
	FROM CTE 
	WHERE Number < EmpQty 
)
--幫員工做一個排序
,CTE2 as (
	select * , row_number() over (order by [ID] ) rnk from Emp
)
--Center資料做一個排序，特別使用站點排名 + @v_firstEmp員工的排名-2 做%的基準數
,CTE3 as (
	SELECT @v_date [Datetime],ID [CenterID]
		,( row_number() over (order by [ID]) 
			+ (select rnk - 2 from CTE2 where [ID] = @v_firstEmp )
		) rnk
	FROM CTE
)
insert into sch 
	select T1.[Datetime],T1.[CenterID],T2.[ID] 
	from CTE3 T1
	left join CTE2 T2 on ((T1.rnk) % (@v_empCount))  = T2.rnk-1;
exec SP_SCH_Center_ByEmp '2018-07-27';
exec SP_SCH_Center_ByEmp '2018-07-28';

select * from SCH;


Datetime
CenterID
EmpID


27/07/2018 00:00:00
1
AA

27/07/2018 00:00:00
1
BB

27/07/2018 00:00:00
2
CC

27/07/2018 00:00:00
2
DD

27/07/2018 00:00:00
2
AA

27/07/2018 00:00:00
3
BB

27/07/2018 00:00:00
3
CC

28/07/2018 00:00:00
1
DD

28/07/2018 00:00:00
1
AA

28/07/2018 00:00:00
2
BB

28/07/2018 00:00:00
2
CC

28/07/2018 00:00:00
2
DD

28/07/2018 00:00:00
3
AA

28/07/2018 00:00:00
3
BB

Datetime	CenterID	EmpID
27/07/2018 00:00:00	1	AA
27/07/2018 00:00:00	1	BB
27/07/2018 00:00:00	2	CC
27/07/2018 00:00:00	2	DD
27/07/2018 00:00:00	2	AA
27/07/2018 00:00:00	3	BB
27/07/2018 00:00:00	3	CC
28/07/2018 00:00:00	1	DD
28/07/2018 00:00:00	1	AA
28/07/2018 00:00:00	2	BB
28/07/2018 00:00:00	2	CC
28/07/2018 00:00:00	2	DD
28/07/2018 00:00:00	3	AA
28/07/2018 00:00:00	3	BB