请问那个高手做过考勤软件。本人现在遇到如下的问题原始打卡记录表data_init(表示该员工上夜班的记录,有其他班次的)
IC NO DATE TIME
---------------------------
000001 2004-01-01 09:00
000001 2004-01-01 13:00
000001 2004-01-01 21:00
000001 2004-01-02 08:00
处理原始打卡记录后的表data_init2
IC NO WORD TYPE DATE IN1 OUT1 IN2 OUT2
---------------------------------------------------------
000001 W 2004-01-01 09:00 13:00 21:00 08:00正常的记录要处理为这样的,请高手能给出一个正常处理数据的方案吗?
即如何做数据处理?
IC NO DATE TIME
---------------------------
000001 2004-01-01 09:00
000001 2004-01-01 13:00
000001 2004-01-01 21:00
000001 2004-01-02 08:00
处理原始打卡记录后的表data_init2
IC NO WORD TYPE DATE IN1 OUT1 IN2 OUT2
---------------------------------------------------------
000001 W 2004-01-01 09:00 13:00 21:00 08:00正常的记录要处理为这样的,请高手能给出一个正常处理数据的方案吗?
即如何做数据处理?
解决方案 »
- text显示数据
- vb ActiveX控件多线程的问题
- 不知道各位大侠有没有遇到类似的问题,就是后台机与前台设备通讯的时候,偶尔出现通讯失败,这是干扰问题呢,还是我程序的问题??
- 如何控制代码屏幕光标的移动(能将sendkeys函数哪样.发送鼠标信号呢?)
- @@@用excel做票据打印的问题,请高手解答
- 如何禁用网络端口?高分流涕痛哭跪求标准答案!!
- 如何通过程序获取读卡器所在的串口、驱动程序状态、启用和停用此设备
- 打包后的安装问题
- vb怎么修改一个已经打开的excel文件?
- 救命啊~~~~~~~~~急急~!!!!!!!!
- listbox数组的使用
- 如何创建一个文本文件,保存用户登陆的帐号和口令呢?以及如何指定路径及写、读文件的信息?
排班: 上午:08:00-12:00 下午:13:30-17:30 加班18:30始
处理原则:
1:上班前30分钟,上班后15分钟有效。 比如8:00上班,则取7:30-8:15之间最大的一笔考勤记录为准。
2:下班后30分钟有效。 比如17:30下班,则取17:30-18:00之间的最大一笔资料。
3:加班不足半小时不算。比如19:00 - 20:22 则只算一个小时。
想要如下结果:
kqm_gh kqm_rq m_in m_out a_in a_out n_in n_out ot
0010006 20030713 07:48 12:13 13:07 17:31 18:22 23:30 5
字段说明如下:
(kqm_gh工号 kqm_rq日期
m_in上午上班 m_out上午下往
a_in下午上班 a_out下午下班
n_in晚上上班 n_out晚上下班
ot加班时数(以半小时为单位))
--排班表说明
description 为说明文字,表明这项代表什么
,在以后的数据处理中意义不太
,如果要让其有作用,后面的数据
处理必须用动态生成SQL的方法
time 为此项目的标准时间,可根据需要设置
min 为提前时间,可根据需要设置
max 为推后时间,可根据需要设置
如果 min 或 max 值为 NULL,视为加班项
*/
--建立考勤表
create table kqm_mstr(kqm_gh varchar(6),kqm_rq int,kqm_sj datetime)
insert into kqm_mstr
select '001006',20030713,'07:48'
union all select '001006',20030713,'08:15'
union all select '001006',20030713,'12:13'
union all select '001006',20030713,'12:14'
union all select '001006',20030713,'13:07'
union all select '001006',20030713,'17:31'
union all select '001006',20030713,'18:32'
union all select '001006',20030713,'23:30'
union all select '001007',20030713,'08:15'
union all select '001007',20030713,'13:07'
union all select '001007',20030713,'17:31'
union all select '001007',20030713,'19:32'
union all select '001007',20030713,'22:30'
--建立排班表
create table tb_pbb(description varchar(10)
,time datetime,min int,max int)
insert into tb_pbb
select '上午-上班','08:00',30,15
union all select '上午-下班','12:00',0,30
union all select '下午-上班','13:30',30,15
union all select '下午-下班','17:30',0,30
union all select '加班','18:30',null,null --如果
--得到结果,如果排班表中的项目不定的话,要用动态表的方法
select a.*,b.[加班-开始],b.[加班-结束],b.加班数
from(
select kqm_gh as 工号,kqm_rq as 日期
,max(case description when '上午-上班' then kqm_sj end) as '上午-上班'
,max(case description when '上午-下班' then kqm_sj end) as '上午-下班'
,max(case description when '下午-上班' then kqm_sj end) as '下午-上班'
,max(case description when '下午-下班' then kqm_sj end) as '下午-下班'
from(
select kqm_gh ,kqm_rq ,convert(varchar(5),max(kqm_sj),108) as kqm_sj
,description
from kqm_mstr a,tb_pbb b
where a.kqm_sj between dateadd(mi,-b.min,b.time) and dateadd(mi,b.max,b.time)
group by kqm_gh ,kqm_rq ,description
) aa group by kqm_gh ,kqm_rq
) a,(
select a.kqm_gh,a.kqm_rq
,convert(varchar(5),max(kqm_sj),108) as [加班-开始]
,convert(varchar(5),min(kqm_sj),108) as [加班-结束]
,datediff(mi,min(kqm_sj),max(kqm_sj))/30 as 加班数
,description
from kqm_mstr a,tb_pbb b
where a.kqm_sj > b.time and b.min is null and b.max is null
group by kqm_gh ,kqm_rq ,description
) b where a.工号=b.kqm_gh and a.日期=b.kqm_rq
drop table kqm_mstr,tb_pbb
---------------------------------------------------------------
按相邻的两个时间配对:
select kqm_gh
,kqm_rq
,(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
) as m_in
,(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
)
) as m_out
,(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj > (select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
)
)
) as a_in
,(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj > (select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
)
)
)
) as a_out
,(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj >(select min(kqm_sj)
from #kqm_mstr
where kqm_gh = T.kqm_gh
and kqm_rq = T.kqm_rq
and kqm_sj > (select min(kqm_sj)
create table kqm_mstr(kqm_gh varchar(6),kqm_rq int,kqm_sj datetime)
insert into kqm_mstr
select '001006',20030713,'07:48'
union all select '001006',20030713,'21:05'
union all select '001006',20030714,'01:33'
union all select '001006',20030713,'02:25'
union all select '001006',20030714,'08:20'
union all select '001007',20030713,'21:10'
union all select '001007',20030714,'01:29'
union all select '001007',20030713,'02:20'
union all select '001007',20030714,'08:10' --建立排班表
create table tb_pbb(description varchar(10)
,time datetime,min int,max int) --為夜班(會誇日的)
insert into tb_pbb
select '下午-上班','21:00',30,15
union all select '明-下班','01:30',0,30
union all select '明-下班','02:30',30,15
union all select '明-下班','08:00',0,30
--得到结果,如果排班表中的项目不定的话,要用动态表的方法
select a.*,b.[加班-开始],b.[加班-结束],b.加班数
from(
select kqm_gh as 工号,kqm_rq as 日期
,max(case description when '上午-上班' then kqm_sj end) as '上午-上班'
,max(case description when '上午-下班' then kqm_sj end) as '上午-下班'
,max(case description when '下午-上班' then kqm_sj end) as '下午-上班'
,max(case description when '下午-下班' then kqm_sj end) as '下午-下班'
from(
select kqm_gh ,kqm_rq ,convert(varchar(5),max(kqm_sj),108) as kqm_sj
,description
from kqm_mstr a,tb_pbb b
where a.kqm_sj between dateadd(mi,-b.min,b.time) and dateadd(mi,b.max,b.time)
group by kqm_gh ,kqm_rq ,description
) aa group by kqm_gh ,kqm_rq
) a,(
select a.kqm_gh,a.kqm_rq
,convert(varchar(5),max(kqm_sj),108) as [加班-开始]
,convert(varchar(5),min(kqm_sj),108) as [加班-结束]
,datediff(mi,min(kqm_sj),max(kqm_sj))/30 as 加班数
,description
from kqm_mstr a,tb_pbb b
where a.kqm_sj > b.time and b.min is null and b.max is null
group by kqm_gh ,kqm_rq ,description
) b where a.工号=b.kqm_gh and a.日期=b.kqm_rq
drop table kqm_mstr,tb_pbb 我要的是這樣,該如果統計????