SQL Server 2017

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

declare @Emp table(
	Emp_ID int
	,Emp_Name nvarchar(50)
)
insert into @Emp
values(1,'AA')
,(2,'BB')
,(3,'CC')
,(4,'DD')

declare @Site table(
	Site_ID int
	,Site_Name nvarchar(50)
	,Site_Num int
)
insert into @Site
values(1,'01',2)
,(2,'02',3)
,(3,'03',2)

declare @Users table(
	Users_Date date
	,Users_Site nvarchar(50)
	,Users_EmpID int
	,Users_Emp nvarchar(50)
)
declare @StartDate date,@EndDate date,@SetDate date,@i int,@Count int
declare @j int,@Sub_Count int,@SetSite nvarchar(50),@SetNum int
set @StartDate = '2018/7/27'
set @EndDate = '2018/7/28'
set @Count = DateDiff(day,@StartDate,@EndDate)
set @i = 0

while(@i<=@Count)
begin
	set @SetDate = dateadd(day,@i,@StartDate)

set @j = 1

select @Sub_Count = count(*)
	from @Site

while(@j<=@Sub_Count)
	begin

select @SetSite = Site_Name
		,@SetNum = Site_Num
		from @Site
		where Site_ID = @j

insert into @Users
		select top (@SetNum)
		@SetDate
		,@SetSite
		,Emp_ID
		,Emp_Name
		from (
			select *
			,isNull((
				select count(*) 
				from @Users
				where Users_EmpID = Emp_ID
			),0) as Emp_Num
			from @Emp
		) as k
		order by Emp_Num

set @j = @j + 1
	end

set @i = @i + 1
end

select Users_Date 
,Users_Site
,Users_Emp
from @Users

Users_Date	Users_Site	Users_Emp
27/07/2018 00:00:00	01	AA
27/07/2018 00:00:00	01	BB
27/07/2018 00:00:00	02	CC
27/07/2018 00:00:00	02	DD
27/07/2018 00:00:00	02	AA
27/07/2018 00:00:00	03	BB
27/07/2018 00:00:00	03	CC
28/07/2018 00:00:00	01	DD
28/07/2018 00:00:00	01	AA
28/07/2018 00:00:00	02	BB
28/07/2018 00:00:00	02	CC
28/07/2018 00:00:00	02	DD
28/07/2018 00:00:00	03	AA
28/07/2018 00:00:00	03	BB