ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 整个月的个人考勤处理

整个月的个人考勤处理

原创 Linux操作系统 作者:zaorv 时间:2019-06-14 12:12:06 0 删除 编辑


--打卡原始记录表
/*
drop table cardOrig
create table cardOrig(
ctime varchar(22)
)
*/
go
--打卡明细表
/*
drop table carddetail
create table cardDetail
(
yearMonth varchar(6), --年月
cday varchar(2), --日
ctime varchar(6), --时分
cardNo varchar (8), --卡号
MachineNo varchar(2) --卡钟号
)
*/
go

--打卡原始记录表(6位年月,2位日期,4位时分,6位卡号,2位卡钟号)
-- 200808 26 2130 007144 01
select top 9 * from cardOrig
go

--打卡明细表
select top 9 * from cardDetail
go

--卡号管理表
create table cardEmp
( cardNo varchar(8),--卡号
empNo varchar(6)--工号
)
--insert cardemp (cardNo) select distinct cardNo from cardDetail

select * from cardEmp

--整个月的个人考勤处理。
--上班取最大打卡时间 ,下班取最小打卡时间
--说明:打卡有效时间范围7:00~8:00,早上上班,1100~1230 早上下班。1235~1400下午上班,1630~1800下午下班;1801~1930加班上班,1931~2230加班下班。
select aa.cardno,aa.yearMonth,aa.cday,a.ftime1,b.ftime2,c.stime1,d.stime2,e.ttime1,f.ttime2
from ( select distinct cardNo,yearMonth,cday from cardDetail where yearMonth='200808') aa
left join
( select max(ctime) as ftime1,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 700 and 830 group by cardNo,yearMonth,cday
) a on aa.cardNo=a.cardNo and aa.cday=a.cday
left join
( select min(ctime) as ftime2,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 1100 and 1230 group by cardNo,yearMonth,cday
) b on aa.cardNo=b.cardNo and aa.cday=b.cday
left join
(select max(ctime) as stime1,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 1235 and 1400 group by cardNo,yearMonth,cday
) c on aa.cardNo=c.cardNo and aa.cday=c.cday
left join
( select min(ctime) as stime2,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 1630 and 1800 group by cardNo,yearMonth,cday
) d on aa.cardNo=d.cardNo and aa.cday=d.cday
left join
(select max(ctime) as ttime1,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 1801 and 1930 group by cardNo,yearMonth,cday
) e on aa.cardNo=e.cardNo and aa.cday=e.cday
left join
(select min(ctime) as ttime2,cardNo,yearMonth,cday from cardDetail where yearMonth='200808' and cast(ctime as int) between 1931 and 2230 group by cardNo,yearMonth,cday
) f on aa.cardNo=f.cardNo and aa.cday=f.cday



select cardNo,ftime,

--将原始记录处理添加到 打卡明细
-- insert cardDetail
select substring(ctime,1,6),substring(ctime,7,2),substring(ctime,9,4),substring(ctime,13,6) ,right(ctime,2) from cardOrig

-- 出勤表
/*
create table attendanceOrig(
empNo varchar(8), --工号
cardNo varchar(8) not null default '', --卡号
yaerMonth varchar(6) not null default '',--年月
cday varchar(2) not null default '',--日
firstIn varchar(6) not null default '',--早上上班时间
firstOut varchar(6) not null default '',--早上下班时间
secondIn varchar(6) not null default '',-- 下午上班时间
secondOut varchar(6) not null default '',--下午下班时间
thirdIn varchar(6) not null default '', --加班上班时间
thirdOut varchar(6) not null default '',--加班下班时间
firstLate varchar(4) not null default '0', --早上迟到分钟
firstLeave varchar(4) not null default '0',--早上早退分钟
secondLate varchar(4) not null default '0',--下午迟到分钟
secondLeave varchar(4) not null default '0',--下午早退分钟
thirdLate varchar(4) not null default '0', --加班迟到分钟
thirdLeave varchar(4) not null default '0' ,--加班早退分钟
lateLeave varchar(4) not null default '0',--迟到早退分钟
overtime varchar(4) not null default '0', --加班小时
firstAbsentIs varchar(4) not null default '0', --早上旷工(是:1,否:0)
secondAbsentIs varchar(4) not null default '0', --下午旷工(是:1,否:0)
thirdAbsentIs varchar(4) not null default '0', --加班旷工(是:1,否:0)
vacationTime varchar(6) not null default '0',-- 休假小时 (从请假表得出)
MachineNo varchar(2) --卡钟号
)
*/
go



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/729024/viewspace-564873/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 字符合并函数
请登录后发表评论 登录
全部评论

注册时间:2018-09-12

  • 博文量
    163
  • 访问量
    120047