教师值班表

--教师值班
alter proc teacher_OnDuty(@hid int)
as
set nocount on
begin
declare @bdate datetime,@edate datetime,@i int,@count_num int,@teacher_num varchar(50),@rand int,@j int,@cfmax int
DECLARE @templist TABLE(id int IDENTITY(1,1),date DATETIME,teacher1 varchar(50),tid1 int,teacher2 varchar(50),tid2 int)
declare @teacher table(id int IDENTITY(1,1),teacher varchar(50))
create table #rand_teacher(id int IDENTITY(1,1),teacher varchar(50),oldid int)
declare @cfid table(id int)

--2个时间段取出时间序列表
SET @i=1
select @bdate=bdate,@edate=edate FROM dwd_185 WHERE hid=@hid
WHILE(@bdate<=@edate)
BEGIN
INSERT INTO @templist(date)
SELECT @bdate
SET @bdate=DATEADD(dd,1,@bdate)
SET @i=@i+1
end
--总数
select @count_num=count(1) from @templist
--所有老师一次
insert into @teacher(teacher)
select EnglishName from teacherinfo where state=0
--辅课老师二次
insert into @teacher(teacher)
select EnglishName from teacherinfo where state=0 and CourseType=1
--教师总数
select @teacher_num=count(1) from @teacher
--随机教师
set @i=1

insert into #rand_teacher(teacher,oldid)
select teacher,id from @teacher order by newid()
while(@i<=@teacher_num)
begin
if exists(select teacher from #rand_teacher where id=@i or id=@i+1 group by teacher having count(*)>1)
begin
truncate table #rand_teacher
insert into #rand_teacher(teacher,oldid)
select teacher,id from @teacher order by newid()
set @i=1
continue
end
set @i=@i+1
end
-- while(@i<=@teacher_num)
-- begin
-- select @rand=cast(ceiling(rand() * @teacher_num) as int)
-- if not exists(select top 1 1 from #rand_teacher a inner join @teacher b on a.oldid=b.id where a.oldid=@rand)
-- begin
-- insert into #rand_teacher(teacher,oldid)
-- select teacher,id from @teacher where id=@rand
-- set @i=@i+1
-- end
-- end
--填入时间
set @j=1
set @i=1
-- while(@i<=@count_num+1)
-- begin
while(@j<=5)
begin
update @templist set teacher1=a.teacher,tid1=a.id from #rand_teacher a,@templist b where a.id=@i and b.id=@j
update @templist set teacher2=a.teacher,tid2=a.id from #rand_teacher a,@templist b where a.id=@i+1 and b.id=@j
set @i=@i+2
set @j=@j+1
end
-- end
-- if exists(select top 1 1 from @templist a where a.teacher1=a.teacher2)
-- begin
-- insert into @cfid(id)
-- select id from @templist a where a.teacher1=a.teacher2
-- set @i=@i+1
-- select @cfmax=count(1) from @cfid
-- set @j=1
-- while(@j<=@cfmax)
-- begin
-- update @templist set teacher2=a.teacher,tid2=a.id from #rand_teacher a,@templist b,@cfid c where b.id=@j
-- set @j=@j+1
-- end
-- end
-- if exists(select top 1 1 from @templist a where a.teacher1=a.teacher2)
-- begin
-- select '1'
-- end
drop table #rand_teacher
insert into tonduty(date,Teacher1,Teacher2)
select date,teacher1,teacher2 from @templist
end