求助考勤分析SQL代码 ( 积分: 200 )

近期需做一个分析考勤数据的过程，比较生疏。还望各位不吝指教
A 班表结构(A)
班别ID 班别名称 可提前打卡时间 早班上班时间 打卡截止时间 可提前打卡时间 早班下班时间 打卡截止时间 可提前打卡时间 中班上班时间 打卡截止时间 可提前打卡时间 中班下班时间 打卡截止时间 可提前打卡时间 晚班上班时间 打卡截止时间 可提前打卡时间 晚班下班时间 打卡截止时间
1 A班 30 8:30 15 30 11:30 15 30 13:30 15 30 17:30 15 30 19:00 15 30 21:00 15
......
班表结构或定义为主从关系，如下：
排班表结构(B)
主表：班别ID 班别名称
1 A班
......
从表：
班别ID 班别打卡次数 可提前打卡时间 上班时间 打卡截止时间 可提前打卡时间 下班时间 打卡截止时间
1 1 30 8:30 15 30 11:30 15
1 2 30 13:30 15 30 17:30 15
1 3 30 19:00 15 30 21:00 15
......
B 员工表结构：员工ID，姓名...
1 张三
......
C 员工排班：
班别ID，员工ID...
1 1
......
D 考勤数据模拟如下:
员工ID 打卡时间
1 2007-3-10 8:08:34
1 2007-3-10 8:08:39 (重复打卡)
1 2007-3-10 11:38:04
1 2007-3-10 13:28:50
1 2007-3-10 17:48:43
1 2007-3-10 19:08:03
1 2007-3-10 21:08:54
1 2007-3-10 21:08:54(重复打卡)
......
E 休假表(可暂不考虑)
要求得出如下结果：
员工ID，考勤日期，班别打卡次数，上班标识，异常否，异常类别，异常时长 （结果说明，无需代码实现）
1 2007-3-10 1 1 0 0 0 考勤正常
1 2007-3-10 1 0 0 0 0 考勤正常
1 2007-3-10 2 1 0 0 0 考勤正常
1 2007-3-10 2 0 0 0 0 考勤正常
1 2007-3-10 3 1 1 1 8 考勤异常，迟到 8 分钟
1 2007-3-10 3 0 0 0 0 考勤正常
......
请各位XDJM有做过的，帮写写考勤结果的SQL分析代码，且不论难与不难，愿给200分(或更多)感谢各位。多谢！

请各位高手帮帮忙！

ding....

你這樣設計分析結果有點不合理吧，其中平均一個員工一天有六筆資料，如果員工人數較多的話數據庫每天的資料是6*員工人數
如果員工人數是1000，每天就有1000*6=6000
個人覺得
员工ID，考勤日期，班别打卡次数，上班标识，异常否，异常类别，异常时长， 班别打卡次数，上班标识，异常否，异常类别，异常时长 ，班别打卡次数，上班标识，异常否，异常类别，异常时长

確切的說我現在維護的考勤系統分析結構和你的是一樣的
工號 姓名 日期 班次 刷卡類 規定刷卡 開始時間 結束時間 實際刷卡 分析結果
60001 張三 2006/12/16 1 正常班 1 2006/12/16 17:30 2006/12/16 17:00 2006/12/16 18:10 2006/12/16 17:32 0
60001 張三 2006/12/16 1 正常班 4 2006/12/16 18:30 2006/12/16 18:00 2006/12/16 19:00 NULL 6
60001 張三 2006/12/16 1 正常班 5 2006/12/16 23:59 2006/12/16 19:00 2006/12/17 04:00 NULL 6
60001 張三 2006/12/16 1 正常班 0 2006/12/16 08:00 2006/12/16 07:15 2006/12/16 08:30 2006/12/16 07:42 0
60001 張三 2006/12/16 1 正常班 1 2006/12/16 12:00 2006/12/16 11:30 2006/12/16 12:45 2006/12/16 12:01 0
60001 張三 2006/12/16 1 正常班 0 2006/12/16 13:30 2006/12/16 13:00 2006/12/16 14:00 2006/12/16 13:21 0

insert table(考勤分析表) id ,fdate,班别打卡次数，上班标识，异常否，异常类别，异常时长 values(1,'2007-3-10' ,1,1,1,0,0) --初始資料都為異常
update table(考勤分析表) set 异常否=0 ,异常类别=0 where 實際刷卡 between 打卡时间 and 截止时间 and 實際刷卡<=上班时间 and 上班标识=0 --處理上班正常
update table(考勤分析表) set 异常否=0 ,异常类别=0 where 實際刷卡 between 打卡时间 and 截止时间 and 實際刷卡>=上班时间 and 上班标识=1 --處理下班正常

谢谢 JoyceSong 的回贴，至于你说的：
你這樣設計分析結果有點不合理吧，其中平均一個員工一天有六筆資料，如果員工人數較多的話數據庫每天的資料是6*員工人數
如果員工人數是1000，每天就有1000*6=6000
個人覺得
员工ID，考勤日期，班别打卡次数，上班标识，异常否，异常类别，异常时长， 班别打卡次数，上班标识，异常否，异常类别，异常时长 ，班别打卡次数，上班标识，异常否，异常类别，异常时长
*******************
我期望的结果应当说是分析的一个临时结果，最终我会转换成这种格式（因为不同岗位的员工可能班别不尽一致，因此我先处理成行结果再做交叉转换）

？你的考勤直接是月考勤處理麼？不是先日考勤處理再月結，怎麼感覺一步完成了
我再仔細看下

我觉得你的表设计的不是太好。如果表分开，实现该程序可能会简单的多。

XD，真的很感谢你。是的，我原来做的是按月分析（改为按日分析倒是容易），因为考虑的因素较多，现在每天几进几出（考勤情况同时存在：每天一进一出，两进两出，或更多进出）就有点犯晕。：（

我應該沒有理解錯吧，你有測試執行過 prAnalyseKqResult 沒？
大概需要多長時間執行完？
大概思路是
1.Exec prGetEmployeeShiftDetail @UserID,@ShiftYear,@ShiftMonth /*得到這個月的排班明細，生成個人每天的詳細資料（ShiftDetail），上下班需打卡的范圍*/
2.用遊標對ShiftDetail的資料一筆筆分析（就是每天的上下班日考勤分析）
如果有100人，30天，那麼ShiftDetail含有資料100*6*30筆記錄
你現在是想自己重新寫一個分析代碼還是？.........

呵呵，怎麼一個月處理一次考勤，那有些異常情況肯定不記得怎麼回事，如果是卡鐘問題或卡問題那不麻煩了
如果你一直用上面代碼處理，那很簡單啊，將這個prGetEmployeeShiftDetail 拆成幾個存儲過程就可以啊，改過的存儲過程隻要年月的參數改成日期參數，適當改改就好了

考勤方法变化，原来是每天上下班各一次，现在需每天打两次或三次进出；所以现在需要重新改写分析过程；
再把实际场景说下：
考勤端在刷卡处：由电脑运行刷卡程序，直接将ID卡的刷卡情况记录到数据库里，员工刷卡成功与否当场即以语音（刷卡处有小喇叭）、文字（有小显示器）反馈
因此就一个月做一次分析，至于你说的异常我们平时就已经知道并作好记录（），月底在分析结果上再根据实际情况予以调整（如确属刷卡程序或电脑故障，则参照当日的记录调整）
BTW：JoyceSong 参照你的代码测试了下不能完成考勤分析(其中涉及到的函数我上面的代码里已贴,同时在分析时并不能如你代码里用 " and 上班标识=0 " 来确定哪些数据是上班，哪些是下班)；谢。如果不打扰你的话，请QQ：756117263
create table #Kq(bsEmployeeId int,bsShiftId int,KQDate datetime,KQTime datetime,OrderNo int,Inbegin
int,InTime datetime,InEnd int,Outbegin
int,OutTime datetime,OutEnd int,isNextDate bit,
isAbn bit,tpAbnId int,OverTime int)
insert into #Kq(bsEmployeeId,bsShiftId,KQDate,KQTime,OrderNo,Inbegin
,InTime,InEnd,Outbegin
,OutTime,OutEnd,isNextDate,
isAbn,tpAbnId,OverTime)
select a.bsEmployeeId,b.Id bsShiftId,null KQDate,null KQTime,c.OrderNo,c.Inbegin
,c.InTime,c.InEnd,c.Outbegin
,c.OutTime,c.OutEnd,c.isNextDate,
1 isAbn,0 tpAbnId,-1 OverTime
from bsEmployeeShift a inner join bsShift b on a.bsShiftID_01 = b.Id
inner join bsShiftDetail c on b.Id = c.bsShiftId
where c.isValid = 1
select * from #Kq
update #Kq set KQDate = b.ADate,KQTime = b.ADate,isAbn = 0 ,tpAbnId = 0,OverTime = 0
from #Kq a inner join dtKqData b on a.bsEmployeeId = b.bsEmployeeId
where b.ADate <= dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.InTime))
--where b.ADate between a.InTime-a.Inbegin
and a.InTime+a.InEnd and b.ADate <= a.InTime --and isOn = 1 --處理上班正常
--迟到
update #Kq set KQDate = b.ADate,KQTime = b.ADate,isAbn = 1 ,tpAbnId = 1,OverTime = datediff(minute,b.ADate,dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.InTime)))
from #Kq a inner join dtKqData b on a.bsEmployeeId = b.bsEmployeeId
where b.ADate > dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.InTime))
--where b.ADate between a.InTime-a.Inbegin
and a.InTime+a.InEnd and b.ADate >= a.InTime --and isOn = 1 --處理上班异常
update #Kq set KQDate = b.ADate,KQTime = b.ADate,isAbn = 0 ,tpAbnId = 0,OverTime = 0
from #Kq a inner join dtKqData b on a.bsEmployeeId = b.bsEmployeeId
where b.ADate >= dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.OutTime))
--where b.ADate between a.OutTime-a.Outbegin
and a.OutTime+a.OutEnd and b.ADate >= a.OutTime --and isOn = 0 --處理下班正常
--早退
update #Kq set KQDate = b.ADate,KQTime = b.ADate,isAbn = 1 ,tpAbnId = 2,OverTime = datediff(minute,b.ADate,dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.OutTime)))
from #Kq a inner join dtKqData b on a.bsEmployeeId = b.bsEmployeeId
where b.ADate < dbo.GetComboDateTimeValue(dbo.GetDateValue(b.ADate),dbo.GetTimeValue(a.OutTime))
--where b.ADate between a.OutTime-a.Outbegin
and a.OutTime+a.OutEnd and b.ADate <= a.OutTime --and isOn = 0 --處理下班异常
select * from #Kq
Drop Table #Kq

不好意思，暫時沒空想分析的問題了，我也在上班有點事情需處理，明天休息。
有空上QQ再聯系你，現在先找其他DFW看下

另外，不知道你那考勤制度是怎樣
是不是同一個人在間隔時間10分鐘內下班再上班？
也就是說有人17：30打下班卡，17：40又打另一個上班卡，所以無法區分上下班卡麼？

同一个ID卡不会出现这种情况，如果是这样，则视作下班打卡时间为：17:30 另一条打卡记录无效；
当然如果另一个班的员工，则可能在上个班次的下班时间段内打上班卡
我的班次安排如(下面为某个班的几次上下班时间)：
班别ID 班别打卡次数 可提前打卡时间 上班时间 打卡截止时间 可提前打卡时间 下班时间 打卡截止时间
1 1 30 8:30 15 30 11:30 15
1 2 30 13:30 15 30 17:30 15
1 3 30 19:00 15 30 21:00 15
提前时间和载止时间指：在考勤时间点的前后多少分钟内打的卡为有效记录，如：
上班时间为：8:30 可提前 30 分钟打卡，截止时间延后 15 分钟，即表示：有效的打卡时间为：8:00 ~ 8:45
无论如何也谢谢你的关注。

班别ID 班别打卡次数 可提前打卡时间 上班时间 打卡截止时间 可提前打卡时间 下班时间 打卡截止时间
1 1 30 8:30 15 30 11:30 15
1 2 30 13:30 15 30 17:30 15
1 3 30 19:00 15 30 21:00 15
你原本不是有一欄是上班標識，上班標識可以區分是上下班的呀，是不是漏了還是寫

insert table(考勤分析表) id ,fdate,班别打卡次数，上班标识，异常否，异常类别，异常时长 values(1,'2007-3-10' ,1,1,1,0,0) --初始資料都為異常
update table(考勤分析表) set 异常否=0 ,异常类别=0 where 實際刷卡 between 打卡时间 and 截止时间 and 實際刷卡<=上班时间 and 上班标识=0 --處理上班正常
update table(考勤分析表) set 异常否=0 ,异常类别=0 where 實際刷卡 between 打卡时间 and 截止时间 and 實際刷卡>=上班时间 and 上班标识=1 --處理下班正常
可能是上面這裡誤導你了， 其中中insert values(1,'2007-3-10' ,[red]1[/red],1,1,0,0)我寫死 上班标识=1

谢谢，我先试试。耽误你了

多人接受答案了。