34,594
社区成员
发帖
与我相关
我的任务
分享
create table p (id int,cqxs int,pcjb int,jiarjb int)
insert p select 1,164,40,32
insert p select 2,2,3,10
create table t
(
T_ID int identity(1,1)
,id int,
rq varchar(20),
week varchar(20),
cqxs varchar(20),
pcjb varchar(20),
jiarjb varchar(20)
)
INSERT INTO t(id,rq,week,cqxs,pcjb,jiarjb)
SELECT 1,'01','六','','','' UNION ALL
SELECT 1,'02','日','','','' UNION ALL
SELECT 1,'03','一' ,'','',''UNION ALL
SELECT 2,'03','一' ,'','',''UNION ALL
SELECT 1,'04','二','','','' UNION ALL
SELECT 1,'05','三','','','' UNION ALL
SELECT 1,'06','四','','','' UNION ALL
SELECT 1,'07','五','','','' UNION ALL
SELECT 1,'08','六','','','' UNION ALL
SELECT 1,'09','日','','',''UNION ALL
SELECT 2,'09','日','','',''UNION ALL
SELECT 1,'10','一','','','' UNION ALL
SELECT 1,'11','二','','','' UNION ALL
SELECT 1,'12','三','','','' UNION ALL
SELECT 1,'13','四','','','' UNION ALL
SELECT 2,'13','四','','','' UNION ALL
SELECT 1,'14','五','','','' UNION ALL
SELECT 1,'15','六','','','' UNION ALL
SELECT 1,'16','日','','','' UNION ALL
SELECT 2,'16','日','','','' UNION ALL
SELECT 1,'17','一' ,'','',''UNION ALL
SELECT 1,'18','二','','','' UNION ALL
SELECT 1,'19','三','','','' UNION ALL
SELECT 1,'20','四','','','' UNION ALL
SELECT 1,'21','五','','','' UNION ALL
SELECT 1,'22','六','','','' UNION ALL
SELECT 1,'23','日','','','' UNION ALL
SELECT 1,'24','一','','','' UNION ALL
SELECT 1,'25','二','','','' UNION ALL
SELECT 1,'26','三','','','' UNION ALL
SELECT 1,'27','四','','','' UNION ALL
SELECT 1,'28','五','','','' UNION ALL
SELECT 1,'29','四','','','' UNION ALL
SELECT 1,'30','五','','' ,''
go
create proc p_plan
as
begin
declare @ts int,@jb int
select @ts = 8,@jb = 2
select ts = identity(int,1,1),cq=0,jb =0,id,rq into #cq from (select top 100 percent a.id,rq from t a,p b where a.id = b.id and week not in ('六','日') order by a.id,newid()) a
select ts = identity(int,1,1), jrjb =0,id,rq into #jr from (select top 100 percent a.id,rq from t a,p b where a.id = b.id and week in ('六','日') order by a.id,newid()) a
if exists(select 1 from p a,
(select id,ts = count(*) from #cq group by id) b,
(select id,ts = count(*) from #jr group by id) c
where a.id = b.id and a.id = c.id and
(cqxs*1.0/b.ts > @ts or jiarjb*1.0/c.ts > @ts or pcjb *1.0/b.ts > @jb))
begin
print '存在违规计划数据'
return
end
update a set cq= @ts*(a.ts-b.ts),jb=@jb*(a.ts-b.ts) from #cq a,(select id,min(ts)-1 ts from #cq group by id) b where a.id = b.id
update a set jrjb=@ts*(a.ts-b.ts) from #jr a,(select id,min(ts)-1 ts from #jr group by id) b where a.id = b.id
--cqxs 按照8小时计算,pcjb 按照2 小时,最后一天为剩余小时。
update a set cqxs = b.cqxs,pcjb=b.pcjb from t a,(select a.id,rq
,cqxs=case when cqxs >=cq then @ts else case when cqxs+@ts>cq then cqxs+@ts-cq else 0 end end
,pcjb=case when pcjb >=jb then @jb else case when pcjb+@jb>jb then pcjb+@jb-jb else 0 end end
from p a,#cq b where a.id = b.id) b where a.id =b.id and a.rq= b.rq
--假日按8小时算,
update a set jiarjb =b.jiarjb from t a,(select a.id,rq
,jiarjb=case when jiarjb >=jrjb then @ts else case when jiarjb+@ts>jrjb then jiarjb+@ts-jrjb else 0 end end
from p a,#jr b where a.id = b.id) b where a.id =b.id and a.rq= b.rq
select * from t order by id,rq
end
go
p_plan
/*
T_ID id rq week cqxs pcjb jiarjb
----------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 1 01 六 8
2 1 02 日 0
3 1 03 一 0 0
4 2 03 一 2 2
5 1 04 二 8 2
6 1 05 三 8 2
7 1 06 四 8 2
8 1 07 五 8 2
9 1 08 六 0
10 1 09 日 0
11 2 09 日 2
12 1 10 一 8 2
13 1 11 二 8 2
14 1 12 三 8 2
15 1 13 四 8 2
16 2 13 四 0 1
17 1 14 五 4 0
18 1 15 六 8
19 1 16 日 8
20 2 16 日 8
21 1 17 一 8 2
22 1 18 二 8 2
23 1 19 三 8 2
24 1 20 四 8 2
25 1 21 五 8 2
26 1 22 六 8
27 1 23 日 0
28 1 24 一 8 2
29 1 25 二 8 2
30 1 26 三 8 2
31 1 27 四 8 2
32 1 28 五 8 2
33 1 29 四 8 2
34 1 30 五 8 2
*/
go
drop table t,p
go
drop proc p_plan