由于系统系统调取排班信息，所以安排系统进行排班处理00网排班需求1.共三个个班次，正常班，中班、中长班，中班随机穿插在正常班中，中长班随机穿插在中
班中。2.CD两个分组,每个大组分四个小组，如C1,C2,C3,C4,D1,D2,D3,D4。每日需3个中班，C组2
个中班时，D组1个中班；中班人数分配一周轮换一次。中长班班所需人数共2人，从CD组各
安排一个。3.一个月总休息天数和当月周末数相同，当周周末有上班，只能安排在相邻的两个周休息。
同一人周末两天不可连续上班。每人连续上班天数不得超过5天。4.中长班次日必须为休息或中班。仅在附表一中人员中安排中长班(尽量均等)，附表二中人
员不可安排中长班。5.CD组同一天休息总人数不可大于3人，周末除外。6.周末值班人数正常班4人、中班4人、中长班2人。7.遇节假日3天休息，值班3天班次不受周六日影响，3天所上班次相同。
主站排班表需求如果A1、A2组今天是呼入，那A3、A4组就是呼出，呼入呼出是每天轮流的，红人只会出现在呼出组 ，每天3人，一个月大家红人天数均等早班班次安排1个晚班，0个长班。中班班次安排2个晚班，3个长班。晚班及长班包括主站客
服及旺旺客服总和。1.共四个班次，早班，中班，长中班，晚班，早中班按周轮，周日到下周六为一轮，晚班随
机穿插在早中班。2.AB两个分组,每个大组分四个小组，如A1,A2,A3,A4,B1,B2,B3,B4。A组上早班时，B组上中
班，晚班所需人数共N人（N可自行设定），从AB组各安排一半，非双数时中班安排多一个。3.一个月总休息天数和当月周末数相同，当周周末有上班，只能安排在相邻的两个周休息。
同一人周末两天不可连续上班。4.晚班次日必须为休息，长晚班次日必须为晚班或休息。仅在附表一中人员中安排长晚班(
尽量均等)，附表二中人员不可安排长晚班，非附表一中其他人员晚班数要求尽量均等。5.1，2小组同一天休息人数不可大于大组人数的1/4，周末除外。3，4小组同样要求。6.周末值班人数可自行设定。7.遇节假日3天休息，A组3天班次不受周六日影响，3天所上班次相同。a.根据周进行AB组早中班交替，之前有排班记录的，要接上之前最后周的来交替b.计算每个人早中晚班上班数量，红人等属性（属性可以后续加）
declare @yearmonth int set @yearmonth=11delete tbCustomerScheduled where yearmonth=@yearmonth
declare @maxleave int set @maxleave=4--设置每天最大休息人数为总人数的 1/@maxleavedeclare
@from datedeclare
@end datedeclare @date datedeclare
@i intdeclare
@count intdeclare @worktype intdelete from tbCustomerScheduledTempset @from=(select fromdate from tbCustomerScheduledRecord where id=@yearmonth)set @end=(select enddate from tbCustomerScheduledRecord where id=@yearmonth)
--begin 顺序排早中班开始，默认AB组循环上早中班，手动排班不变set @from=(select fromdate from tbCustomerScheduledRecord where id=@yearmonth)set @end=(select enddate from tbCustomerScheduledRecord where id=@yearmonth)
--插入手工排班班表begininsert into tbCustomerScheduled(customer,worktype,workday,memo,yearmonth,adddate,handinsert)select customer,worktype,workday,memo,yearmonth,adddate,1 from tbCustomerScheduledHand sdhwhere yearmonth=@yearmonth and not exists(select top 1 1 from tbCustomerScheduled sd1 where sdh.customer=sd1.customer and sd1.workday=sdh.workday and sd1.yearmonth=sdh.yearmonth)
declare @morning varchardeclare @noon varcharset @morning=(select ISNULL((select 'A' from tbCustomerScheduledRecord where id=@yearmonth and ismorning=0),'B'))set @noon = (select case when @morning='A' then 'B' else 'A' end)--;//B组分中班
declare @datetemp date
set @datetemp = @
while (@datetemp &= @end)
if (select
case when datename(weekday,@datetemp)='星期日' then 0
when datename(weekday,@datetemp)='星期一' then 1
when datename(weekday,@datetemp)='星期二' then 2
when datename(weekday,@datetemp)='星期三' then 3
when datename(weekday,@datetemp)='星期四' then 4
when datename(weekday,@datetemp)='星期五' then 5
when datename(weekday,@datetemp)='星期六' then 6
else 0 end)
case when datename(weekday,dateadd(d,-1,@datetemp))='星期日' then 0
when datename(weekday,dateadd(d,-1,@datetemp))='星期一' then 1
when datename(weekday,dateadd(d,-1,@datetemp))='星期二' then 2
when datename(weekday,dateadd(d,-1,@datetemp))='星期三' then 3
when datename(weekday,dateadd(d,-1,@datetemp))='星期四' then 4
when datename(weekday,dateadd(d,-1,@datetemp))='星期五' then 5
when datename(weekday,dateadd(d,-1,@datetemp))='星期六' then 6
else 0 end)
set @morning = @
if @morning='A'
set @noon='B'
set @noon='A'
select @morning
insert into tbCustomerScheduled(customer,worktype,workday,yearmonth)
select wd.customer,(select case when p.CustomerGroupBig=@morning then 1 when p.CustomerGroupBig=@noon then 2 else 1 end ),@datetemp,@yearmonth
from P_User p inner join tbCustomerScheduledWorkDay wd on p.userName=wd.customer
where wd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1
where wd.customer=sd1.customer and sd1.workday=@datetemp and sd1.yearmonth=@yearmonth)
set @datetemp = dateadd(d,1,@datetemp)
endend--end 顺序排早中班结束
--排班中班早班旺旺和中民客服分开，分两步排班set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)--begin 排中班开始set @worktype=2
--主站客服 begin--算出主站客服还剩多少天中班未上set @count=(select nooncount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.yearmonth=@yearmonth and d.workday=tbCustomerScheduledDutyDay.workdate and handinsert=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --查询出适合上中班的主站客服，且是主站客服 --中班的情况一般是周末，周末不让连着上班 --当天前一天跟当天上班类型一样或者前一天是休息才排班 --前6天不是一直上班的才排班 --前后9天没手动排过早中班的才排班（最大化防止周末连着上班） --假期之前安排过最多的排序上班，防止假期不够排 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduled sd where sd.worktype=@worktype and handinsert=0 and @date=sd.workday
and sd.yearmonth=@yearmonth and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0)) and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer) and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype&&0)&=6 and not exists(select top 1 1 from tbCustomerScheduled sd2
where handinsert=1 and sd2.yearmonth=@yearmonth and sd.customer=sd2.customer and sd2.worktype in(1,2) and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date)) order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID() set @count = @count-@@rowcount
--如果上面条件没找到客服或者客服不够上中班 --排查9天上班限制（周末连着上班） if @count&0 begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday
and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype&&0)&=6
--and not exists(select top 1 1 from tbCustomerScheduled sd2
--where handinsert=1 and sd.customer=sd2.customer and sd2.worktype in(1,2)
--and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID() end
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduled set worktype=0
where workday=@date and handinsert=0 and worktype=@worktype and yearmonth=@yearmonth and customer not in(select customer from tbCustomerScheduledTemp)end--主站客服 end
--end 排中班结束
--begin 排早班开始set @worktype=1--主站客服beginset @count=(select morningcount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.workday=dd.workdate and handinsert=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay dd where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --跟中班规则一样 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduled sd where sd.worktype=@worktype and handinsert=0 and @date=sd.workday
and sd.yearmonth=@yearmonth and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0)) and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer) and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype&&0)&=6 and not exists(select top 1 1 from tbCustomerScheduled sd2
where handinsert=1 and sd2.yearmonth=@yearmonth and sd.customer=sd2.customer and sd2.worktype in(1,2,3) and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date)) order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID() set @count = @count-@@rowcount
--跟中班规则一样 if @count&0 begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday
and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype&&0)&=6
--and not exists(select top 1 1 from tbCustomerScheduled sd2
--where handinsert=1 and sd.customer=sd2.customer and sd2.worktype in(1,2,3)
--and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID() end
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduled set worktype=0 where workday=@date and handinsert=0 and worktype=@worktype and yearmonth=@yearmonth and customer not in(select customer from tbCustomerScheduledTemp)
end --主站客服 end
--早班结束
set @i=@i+1end
--设置每个客服该上晚班的天数update tbCustomerScheduledWorkDay set nightdaytemp=nightday-(select COUNT(1) from tbCustomerScheduled d where d.worktype=3 and d.yearmonth=@yearmonth and d.customer=tbCustomerScheduledWorkDay.customer and yearmonth=@yearmonth) where yearmonth=@yearmonth
--begin 排晚班开始set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)set @worktype=3set @count=(select nightcount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.yearmonth=@yearmonth and d.workday=tbCustomerScheduledDutyDay.workdate and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --晚班优先排在6天班之内，且6天班前后不排晚班（最大的减少6天班的存在） --在非手工排班内，安排系统排班的早中班选择晚班 --晚班后一天且不为手工排班 --9天内上过晚班的不排晚班 --晚班还剩最多的排序，假期最少的排序 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.nightdaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2)
and handinsert=0 and sd.customer=wk.customer and
@date=sd.workday and sd.yearmonth=@yearmonth and not exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.handinsert=1)) and (((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,1,@date) and sd1.worktype&&0)=2 and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype&&0)=5) or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,2,@date) and sd1.worktype&&0)=3 and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-3,@date) and @date and sd1.worktype&&0)=4) or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,3,@date) and sd1.worktype&&0)=4 and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-2,@date) and @date and sd1.worktype&&0)=3) or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,4,@date) and sd1.worktype&&0)=5 and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer
and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-1,@date) and @date and sd1.worktype&&0)=2)
and not exists(select top 1 1 from tbCustomerScheduled sd2 where sd2.customer=wk.customer and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date)) and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer) order by nightdaytemp desc,(select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,NEWID()
set @count = @count-@@rowcount
--如果当天的还没有安排完，则往下走 --在非手工排班内，安排系统排班的早中班选择晚班 --晚班后一天且不为手工排班 -- if @count&0 begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where wk.nightdaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and handinsert=0 and sd.customer=wk.customer and
@date=sd.workday and sd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth
and sd.workday=dateadd(d,-1,sd1.workday) and sd1.handinsert=1))
and not exists(select top 1 1 from tbCustomerScheduled sd2 where sd2.customer=wk.customer
and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.workday=DATEADD(d,1,@date) and sd1.yearmonth=@yearmonth
and sd1.worktype=0 and sd1.handinsert&&1)
--and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.workday=dateadd(d,-5,@date) and sd1.worktype=0)
--and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype&&0)=5
order by nightdaytemp desc,(select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,NEWID()
set @count = @count-@@rowcount
if @count&0--如果当天的还没有安排完，则随机安排还剩夜班最多的客服
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk
where wk.nightdaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd
where sd.worktype in (0,1,2) and handinsert=0
and sd.yearmonth=@yearmonth
and sd.customer=wk.customer and
@date=sd.workday
and not exists(select top 1 1 from tbCustomerScheduled sd1
where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth
and sd.workday=dateadd(d,-1,sd1.workday) and sd1.handinsert=1 and sd1.worktype not in(0,3)))
and not exists(select top 1 1 from tbCustomerScheduled sd2
where sd2.customer=wk.customer
and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday
between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,nightdaytemp desc,NEWID()
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set nightdaytemp=nightdaytemp-1 where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonthend set @i=@i+1end--end 排晚班结束
--begin 晚班之后接着休息日update sd set worktype=0 from (select customer,dateadd(d,1,workday) workday from tbCustomerScheduled sd where worktype=3 and yearmonth=@yearmonth and dateadd(d,1,workday) between (select fromdate from tbCustomerScheduledRecord where id=@yearmonth) and (select enddate from tbCustomerScheduledRecord where id=@yearmonth))sd1 inner join tbCustomerScheduled sd on sd1.customer=sd.customer and sd1.workday=sd.workday and sd.yearmonth=@yearmonth and sd.handinsert=0 --然后计算客服还剩多少假日update tbCustomerScheduledWorkDay set leavedaytemp=leaveday-(select COUNT(1) from tbCustomerScheduled d where d.worktype=0 and d.yearmonth=@yearmonth
and d.customer=tbCustomerScheduledWorkDay.customer and d.yearmonth=@yearmonth) where yearmonth=@yearmonth
--这里处理旺旺客服的休息天数declare @leavecustomer int declare @sexworkcount int--循环查找上班6天的客服，3次应该够了，如果有6天班的情况这里就要多循环几次--或者去安排6天班的第二天休息，这里只安排第三、第四天休息set @sexworkcount=3while @sexworkcount&0 and exists(select top 1 1 from tbCustomerScheduled sdwhere (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-5,sd.workday) and sd.workday and sd1.worktype&&0)&5 and sd.yearmonth=@yearmonth)begin--排假日日期全部没安排休假的客服全部安排休假set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)set @worktype=0--主站客服 begindelete tbCustomerScheduledTemp--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。--查询出今天能安排的休息人数，对于周末已经安排了--主站客服休息人数set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--主站客服，最大可休息人数set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth)/@maxleave-@leavecustomer--有没排班主站客服的且最大休息人数大于0就去寻找最优休息客服if @count&0 and (select COUNT(1) from tbCustomerScheduled sd where handinsert=0 and sd.yearmonth=@yearmonth )&0begin --前后有连续上班6天的，如果当天不是手动排班，设置休息日。如果客服有休息日的时候
insert into tbCustomerScheduledTemp(customer) select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and(((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-2,@date) and @date and sd1.worktype&&0)=3 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,3,@date) and sd1.worktype&&0)=4) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-3,@date) and @date and sd1.worktype&&0)=4 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,2,@date) and sd1.worktype&&0)=3) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-1,@date) and @date and sd1.worktype&&0)=2 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,4,@date) and sd1.worktype&&0)=5) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype&&0)=5 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,1,@date) and sd1.worktype&&0)=2) ) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A') union select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date) and(((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-2,@date) and @date and sd1.worktype&&0)=3 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,3,@date) and sd1.worktype&&0)=4) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-3,@date) and @date and sd1.worktype&&0)=4 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,2,@date) and sd1.worktype&&0)=3) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-1,@date) and @date and sd1.worktype&&0)=2 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,4,@date) and sd1.worktype&&0)=5) or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype&&0)=5 and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,1,@date) and sd1.worktype&&0)=2) ) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1 where customer in(select customer from tbCustomerScheduledTemp) and yearmonth=@yearmonthend --主站客服end
set @i=@i+1end
set @sexworkcount=@sexworkcount-1end--end 排假日结束
--排假日日期全部没安排休假的客服全部安排休假set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)set @worktype=0--主站客服 begindelete tbCustomerScheduledTemp--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。--查询出今天能安排的休息人数，对于周末已经安排了--declare @workcustomer int set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--最大可休息人数set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth )/@maxleave-@leavecustomer--有没排班的且最大休息人数大于0就去寻找最优休息客服if @count&0 and (select COUNT(1) from tbCustomerScheduled where handinsert=0
and yearmonth=@yearmonth )&0begin --这里优先安排休息日之后再休息 insert into tbCustomerScheduledTemp(customer) select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A') union
select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1 where customer in(select customer from tbCustomerScheduledTemp) and yearmonth=@yearmonthend --主站客服 end
set @i=@i+1end--end 排假日结束
--排假日日期全部没安排休假的客服全部安排休假--防止还有人没安排休假的情况，随机安排set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)set @worktype=0--主站客服 begindelete tbCustomerScheduledTemp--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。--查询出今天能安排的休息人数，对于周末已经安排了--declare @workcustomer int set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--最大可休息人数set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth )/@maxleave-@leavecustomer--有没排班的且最大休息人数大于0就去寻找最优休息客服if @count&0 and (select COUNT(1) from tbCustomerScheduled where handinsert=0
and yearmonth=@yearmonth )&0begin --这里优先安排休息日之后再休息 insert into tbCustomerScheduledTemp(customer) select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A') union
select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth
and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
--and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0) and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1 where customer in(select customer from tbCustomerScheduledTemp) and yearmonth=@yearmonthend --主站客服 end
set @i=@i+1end--end 排假日结束 --中长班 begin--设置每个客服该上中长班的天数,使用longnoontemp做临时变量使用update tbCustomerScheduledWorkDay set longnoontemp=longnoon-(select COUNT(1) from tbCustomerScheduled d where d.longnoon&&0 and d.customer=tbCustomerScheduledWorkDay.customer and yearmonth=@yearmonth) where yearmonth=@yearmonth
set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)set @count=(select longnooncount-(select COUNT(1) from tbCustomerScheduled d where d.longnoon&&0 and d.workday=tbCustomerScheduledDutyDay.workdate and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --选择当天是中班的客服 --优先排班后一天是休息的客服 --中长班还剩最多的排序 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.longnoontemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2 and sd.customer=wk.customer and
@date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.worktype in(0,3)))
order by longnoontemp desc,NEWID()
set @count = @count-@@rowcount
--如果当天的还没有安排完，则往下走 --在非手工排班内选择当天是中班的客服 --次之，排班，后一天是中中班的客服 --中长班还剩最多的排序 if @count&0 begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.longnoontemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2
and sd.customer=wk.customer and
@date=sd.workday and sd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1
where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.worktype in(0,1,3)))
order by longnoontemp desc,NEWID() end
update tbCustomerScheduled set longnoon=1 where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set longnoontemp=longnoontemp-1 where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonthend set @i=@i+1end
end--中长班
/*--红人 早班红人，中班红人 begin--设置每个客服该上红人的天数,使用isredtemp做临时变量使用update tbCustomerScheduledWorkDay set isredtemp=isred-(select COUNT(1) from tbCustomerScheduled d where d.isred&&0 and d.customer=tbCustomerScheduledWorkDay.customer
and yearmonth=@yearmonth) where yearmonth=@yearmonth
set @i=0while @i&=DATEDIFF(d,@from,@end)beginset @date=DATEADD(d,@i,@from)
--早班红人开始beginset @count=(select isredcount-(select COUNT(1) from tbCustomerScheduled d where d.isred&&0 and d.workday=tbCustomerScheduledDutyDay.workdate and d.worktype=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --选择当天是早班的客服 --红人还剩最多的排序 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.isredtemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=1
and sd.customer=wk.customer and
@date=sd.workday)
order by isredtemp desc,NEWID()
update tbCustomerScheduled set isred=1 where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set isredtemp=isredtemp-1 where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonthend end --早班结束
--中班红人开始beginset @count=(select isrednooncount-(select COUNT(1) from tbCustomerScheduled d where d.isred&&0 and d.workday=tbCustomerScheduledDutyDay.workdate and d.worktype=2 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)delete tbCustomerScheduledTempif @count&0begin --选择当天是中班的客服 --红人还剩最多的排序 insert into tbCustomerScheduledTemp(customer) select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.isredtemp&0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2 and sd.customer=wk.customer and
@date=sd.workday)
order by isredtemp desc,NEWID()
update tbCustomerScheduled set isred=1
where workday=@date
and yearmonth=@yearmonth and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set isredtemp=isredtemp-1 where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonthend end --中班红人结束
set @i=@i+1end
end--红人 */
支持原创：如有问题或者不同见解请联系
阅读(...) 评论()