我是做考勤的,现在想做一份假的考勤,即我手上有每个员工的出勤小时、加班小时(总数)
如:
张三 出勤小时 平时加班小时 假日加班
168 40 32
.
.
. 现在要的结果是,能不能建一个存储过程,将这些出勤小时、平时加班小时随机分配到当月的每天,而且每天不能超过一个数(出勤小时可以是8,如果时间不够,那么有几天出勤小时就没有,平时加班小时可以有三个数据机选(1,1.5,2))或者是不超过3小时,假日加班小是只有在周未分配,而这些每天的数的总和要与给的小时一样。
说明一点就是倒推,顺推我已经做出来了,可倒推头大 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 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 1,'10','一','','','' UNION ALL
SELECT 1,'11','二','','','' UNION ALL
SELECT 1,'12','三','','','' UNION ALL
SELECT 1,'13','四','','','' UNION ALL
SELECT 1,'14','五','','','' UNION ALL
SELECT 1,'15','六','','','' UNION ALL
SELECT 1,'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','五','','' ,''
如:
张三 出勤小时 平时加班小时 假日加班
168 40 32
.
.
. 现在要的结果是,能不能建一个存储过程,将这些出勤小时、平时加班小时随机分配到当月的每天,而且每天不能超过一个数(出勤小时可以是8,如果时间不够,那么有几天出勤小时就没有,平时加班小时可以有三个数据机选(1,1.5,2))或者是不超过3小时,假日加班小是只有在周未分配,而这些每天的数的总和要与给的小时一样。
说明一点就是倒推,顺推我已经做出来了,可倒推头大 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 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 1,'10','一','','','' UNION ALL
SELECT 1,'11','二','','','' UNION ALL
SELECT 1,'12','三','','','' UNION ALL
SELECT 1,'13','四','','','' UNION ALL
SELECT 1,'14','五','','','' UNION ALL
SELECT 1,'15','六','','','' UNION ALL
SELECT 1,'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','五','','' ,''
解决方案 »
- sql存储过程语句怎么改成asp里面的sql语句
- 商品CD,商品名查询
- 关于count统计问题
- 请问各位高人,一个成品库存问题?
- SQLSERVER不能附加数据库文件,请大侠帮我看看。
- 大家请来讨论一下MSSQLSVR上sleeping状态
- SQL 怎么取某一字段不同的记录
- 在线求助了!等!
- 求一个VARBINARY(32)的查询方式
- 小白跪求大佬解决
- 关于索引的疑问?
- VB的Timer1事件每隔100毫秒从服务器数据库的TabA表中提数据刷新客户端界面上的MSHFlexGrid1控件。有24个客户端,TabA表中的记录由于各个客户端的操作而频繁变动,变动的范围为0~2000条左右记录,请审查这个方案是否可行。见主要代码:
insert p select 1,164,40,32
insert p select 2,2,3,10create 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