不考虑加班，请假，外出之类。
原始记录表:
1：工作日历表calendar
2：排班表worktime
3：人事资料表employee
4：异常类别表(迟到，早退，无刷卡...)abnormity
5：考勤记录表timecard
处理结果表：(可以用表或视图的形式存放)
1: 打卡异常 10001 20031102 上午上班 迟到
2：考勤明细表1： 10001 20031101 07:55/12:01/12:02/13:55/18:00/18:01 列出所有打卡明细
3：考勤明细表2： 10001 20031101 07:55 12:01 13:55 18:00
/*工作日历表：
如下假设只有三种工作日历，默认情况如下
1：星期六和星期天不上班
2：星期天不上班
4：每天都上班
*/
create table calendar(
date smalldatetime primary key clustered,
flag1 bit,
flag2 bit,
flag3 bit
)
exec sp_addextendedproperty n'ms_description', n'日期', n'user', n'dbo', n'table', n'calendar', n'column', n'date'
exec sp_addextendedproperty n'ms_description', n'是否上班1', n'user', n'dbo', n'table', n'calendar', n'column', n'flag1'
exec sp_addextendedproperty n'ms_description', n'是否上班2', n'user', n'dbo', n'table', n'calendar', n'column', n'flag2'
exec sp_addextendedproperty n'ms_description', n'是否上班3', n'user', n'dbo', n'table', n'calendar', n'column', n'flag3'
/* 个别录入资料
insert calendar select '20031101',0,1,1
union all select '20031102',0,0,1
union all select '20031103',1,1,1
union all select '20031104',1,1,1
*/
/*********如下存储过程用于批量录入资料**************/
create procedure addworkcalendar @bdate smalldatetime,@edate smalldatetime
as
declare @cdate smalldatetime
declare @flag1 bit
declare @flag2 bit
declare @flag3 bit
set @cdate = @bdate
if exists (select * from calendar where cld_rq between @bdate and @edate)
begin
raiserror('已有该范围的资料，请核对开始日期和结束日期!!!',16,-1)
return
end
while @cdate <= @edate
begin
set @flag1 = (case when datepart(weekday,@cdate) in (1,7) then 0 else 1 end)
set @flag2 = (case when datepart(weekday,@cdate)=1 then 0 else 1 end)
set @flag3 = 1
insert calendar values(@cdate,@flag,@flag1,@flag2)
set @cdate = dateadd(day,1,@cdate)
end
/******** usage:
exec addworkcalendar '20031101','20031130'
*********/
/*************
当然，更灵活的方法是如下方式建表：
日期 方式 标志
20011101 1 0
20011101 2 1
20011101 3 1
...
********************/
/*
2:排班表(为简单起见，不考虑加班的排班，且只考虑一天最多需四次刷卡的情况)
*/
create table worktime(
worktimeid int primary key clustered,
minb char(5),
mins char(5),
mine char(5),
moutb char(5),
mouts char(5),
moute char(5),
ainb char(5),
ains char(5),
aine char(5),
aoutb char(5),
aouts char(5),
aoute char(5)
)
go
exec sp_addextendedproperty n'ms_description', n'班制编号', n'user', n'dbo', n'table', n'worktime', n'column', n'worktimeid'
exec sp_addextendedproperty n'ms_description', n'上午上班始', n'user', n'dbo', n'table', n'worktime', n'column', n'minb'
exec sp_addextendedproperty n'ms_description', n'上午上班标准', n'user', n'dbo', n'table', n'worktime', n'column', n'mins'
exec sp_addextendedproperty n'ms_description', n'上午上班止', n'user', n'dbo', n'table', n'worktime', n'column', n'mine'
exec sp_addextendedproperty n'ms_description', n'上午下班始', n'user', n'dbo', n'table', n'worktime', n'column', n'moutb'
exec sp_addextendedproperty n'ms_description', n'上午下班标准', n'user', n'dbo', n'table', n'worktime', n'column', n'mouts'
exec sp_addextendedproperty n'ms_description', n'上午下班止', n'user', n'dbo', n'table', n'worktime', n'column', n'moute'
exec sp_addextendedproperty n'ms_description', n'下午上班始', n'user', n'dbo', n'table', n'worktime', n'column', n'ainb'
exec sp_addextendedproperty n'ms_description', n'下午上班标准', n'user', n'dbo', n'table', n'worktime', n'column', n'ains'
exec sp_addextendedproperty n'ms_description', n'下午上班止', n'user', n'dbo', n'table', n'worktime', n'column', n'aine'
exec sp_addextendedproperty n'ms_description', n'下午下班始', n'user', n'dbo', n'table', n'worktime', n'column', n'aoutb'
exec sp_addextendedproperty n'ms_description', n'下午下班标准', n'user', n'dbo', n'table', n'worktime', n'column', n'aouts'
exec sp_addextendedproperty n'ms_description', n'下午下班止', n'user', n'dbo', n'table', n'worktime', n'column', n'aoute'
/*
注：在上班始－上班标准间打卡属于正常打卡，在上班标准－上班止间打卡属于迟到
在下班始－下班标准间打卡属于早退，在下班标准－下班止间打卡属于正常打卡
其余时间打卡属于无效打卡。
*/
--3：人事资料表(只列出与考勤有关的字段)
create table employee(
workno char(6) primary key,
[name] varchar(10),
calendarflag int check(calendarflag in (1,2,3) ), -- 1,2，3分别表示工作日历中的标志1，标志2，标志3
worktimeid int -- 排班表中的班制
)
go
exec sp_addextendedproperty n'ms_description', n'工号', n'user', n'dbo', n'table', n'employee', n'column', n'workno'
exec sp_addextendedproperty n'ms_description', n'姓名', n'user', n'dbo', n'table', n'employee', n'column', n'name'
exec sp_addextendedproperty n'ms_description', n'工作日历方式', n'user', n'dbo', n'table', n'employee', n'column', n'calendarflag'
exec sp_addextendedproperty n'ms_description', n'班制', n'user', n'dbo', n'table', n'employee', n'column', n'worktimeid'
/*
calendarflag 1,2，3分别表示工作日历中的标志1，标志2，标志3
worktimeid 对应排班表中的班制
*/
--4：异常类别表abnormity
create table abnormity(
abnormityno int primary key clustered,
[description] varchar(10)
)
go
exec sp_addextendedproperty n'ms_description', n'异常类别', n'user', n'dbo', n'table', n'abnormity', n'column', n'abnormityno'
exec sp_addextendedproperty n'ms_description', n'异常说明', n'user', n'dbo', n'table', n'abnormity', n'column', n'description'
/**************
异常包括:迟到(还可细分成迟到的时间段)，早退，无刷卡...
当然可能还有扣薪方式之类的，此处不予考虑。
*****************/
5：考勤记录表timecard
create table timecard(
workno char(6),
[date] datetime,
constraint [pk_timecard] primary key clustered
(
workno,
[date]
) on [primary]
)
go
exec sp_addextendedproperty n'ms_description', n'工号', n'user', n'dbo', n'table', n'timecard', n'column', n'workno'
exec sp_addextendedproperty n'ms_description', n'打卡时间', n'user', n'dbo', n'table', n'timecard', n'column', n'date'
/*******
其实我实际中是按卡号（对应工号），日期，时间，卡钟号 四个字段来存的。
*******/
/****************如下处理得到 考勤明细表1：**********/
--1.创建一个合并的函数
create function f_kqlist(@workno char(6),@date char(8))
returns varchar(400)
as
begin
declare @str varchar(1000)
set @str=''
select @[email protected]+'/'+ convert(char(8),[date],108) from timecard
where workno = @workno and datediff(day,[date], @date) = 0
set @str=right(@str,len(@str)-1)
return(@str)
end
/* usage:
select distinct workno,date,dbo.f_kqlist(workno,[date])
from timecard
where workno = '102337' and convert(char(8),[date],112) = '20030814'
*/
剩下的留给大家吧。其实也不难，就看谁的效率比较高一些罢了。 :d
(我的做法： 异常处理 : 用存储过程，用游标做的，懒得去优化了
考勤明细表2： 用函数，原来用游标，不好，后来才改用函数的)
