SQL考勤请假时间计算

《SQL考勤请假时间计算》由会员分享，可在线阅读，更多相关《SQL考勤请假时间计算（12页珍藏版）》请在装配图网上搜索。

1、SQL 考勤请假时间计算推荐资料自学网址:alter proc I- 请假时间计算 (userid as varchar(50),-员工代号begindate as datetime ,-开始时间enddate as datetime,-截止时间reportdate as datetime-销假时间asdeclare Shour as floatdeclare hour as float-时间declare day as float-天数declare column as varchar(800) declare Scolumn as varchar(800) declare begin as

2、 datetime declare end as datetime declare report as datetime set begin=begindate set end=enddate set report=reportdatedeclare begintime as varchar(20)-开始时间declare endtime as varchar(20)-截止时间declare Depno as varchar(20)-部门代号declare morring1 as varchar(20)-早上上班时间declare morring2 as varchar(20)-早上下班时间d

3、eclare afternoon1 as varchar(20)-下午上班时间declare afternoon2 as varchar(20)-下午下班时间declare night1 as varchar(20)-晚上上班时间declare night2 as varchar(20)-晚上下班时间declare b as int-开始 日declare e as int declare i as int- 截止 日-select Depno=cardbranchid fromHN_Employee whereuserID=userid-select morring1= CONVERT(va

4、rchar(100),MB, 24) ,morring2=CONVERT(varchar(100),ME, 24) , -afternoon1=CONVERT(varchar(100),AB, 24),afternoon2=CONVERT(varchar(100),AE, 24) , -night1=CONVERT(varchar(100),NB, 24) ,night2=CONVERT(varchar(100),NE, 24)-from HN_KqTimeSet where branchid=Depno and years=year(begin) and months=month(begin

5、)select Depno=刷卡部门from N-基本资料单头where员工代号 =useridselect morring1= CONVERT(varchar(100), 上午上班时间 , 24) ,morring2=CONVERT(varchar(100), 上午下班时间 , 24) , afternoon1=CONVERT(varchar(100), 下午上班时间 , 24),afternoon2=CONVERT(varchar(100), 下午下班时间 ,24) , night1=CONVERT(varchar(100), 晚上上班时间 ,24) ,night2=CONVERT(var

6、char(100), 晚上下班时间 , 24)from N- 作息时间设置 where 部门别 =Depno and 年度 =year(begin) and 月份 =month(begin)if isnull(night1,)= or isnull(night2,)= or night1=00:00:00 or night2=00:00:00 beginset night1=afternoon2set night2=afternoon2end 3 销假if report<>beginif report<endbeginset end=reportendend set b=da

7、y(begin)set e=day(end)-跨天请假是否存在其中有天不用上班 统计跨天时间if b<ebeginset begintime =CONVERT(varchar(100),begin ,23) - 日 期 2014-08-08set i=0while b<=ebeginset column=D+convert(varchar(20),b)CREATE TABLE #KqDayq(KqDay INT)set Scolumn= INSERT INTO #KqDayq(KqDay) select count(*) AS KqDayfrom HN_KqDaySet where

8、branchid=+Depno+ and years=year(+begintime+) and months=month(+begintime+) and +column+=1 exec (Scolumn) declare KqDay as INTSET KqDay=(SELECT TOP 1 KqDay FROM #KqDayq)DROP TABLE #KqDayqif KqDay>0beginset i=i+1endset b=b+1endset i=i-1endelsebeginset i=0end0 请假setday=datediff(minute,morring1,morri

9、ng2)+datediff(minute,afternoon1,afternoon2)+datediff(minute,night1,night2)set day=(i*day)/60 - 天数 * 时间set begintime =CONVERT(varchar(100),begin ,24) - 取时间部分 08:00set endtime =CONVERT(varchar(100),end ,24) - 取时间部分 17:00if begintime<=endtimebeginif begintime <=morring2- 请假开始时间在上午begin - 请假截止时间判断

10、if endtime <=morring2- 截止时间在上午下班内beginset hour= datediff(minute,begintime,endtime)endelse if endtime<=afternoon2- 截止时间在下午班内beginset hour=datediff(minute,begintime,morring2)+datediff(minute,afternoon1,endtime)endelse if endtime <=night2- 截止时间在晚上下班内beginset hour= datediff(minute,begintime,mor

11、ring2)+datediff(minute,aft ernoon1,afternoon2)+datediff(minute,night1,endtime) print hourendendelse if begintime<=afternoon2- 请假开始时间在下午begin- 对请假截止时间判断if endtime<=afternoon2beginset hour=datediff(minute,begintime,endtime) end else if endtime<=night2 beginset hour=datediff(minute,begintime,a

12、fternoon2)+datediff(mi nute,night1,endtime)endendelse ifbegintime<=night2 - 请假开始时间在晚上beginsethour=datediff(minute,begintime,endtime)endendelse -另一种情况如果是跨天请假 截止时间 < 请假开始 时间 先判断截止的时间beginif endtime<=morring2- 截止时间在早上beginif begintime<=morring2 - 请假开始时间在早上beginsethour=datediff(minute,begint

13、ime,endtime)endelse if begintime<=afternoon2 - 请假 开始时间在下午begin- 请假的天数减去去工作的小时sethour=-(datediff(minute,endtime,morring2)+datediff(minute,afternoon1,begintime)endelse if begintime<=night2beginsethour=-(datediff(minute,endtime,morring2)+datediff(minu te,afternoon1,afternoon2)+datediff(minute,nig

14、ht1,begin time)endendelse if endtime<=afternoon2- 截止时间在下午beginif begintime <=afternoon2 -早上不用判 断 判断开始时间在下午beginsethour=datediff(minute,begintime,endtime)- 请假开始截止日期都在下午 相减工作的时间 负数endelse if begintime<=night2- 开始时间在晚上begin sethour=-(datediff(minute,endtime,afternoon2)+datediff(mi nute,night1,

15、begintime)endendelse if endtime<=night2-截止时间在晚上beginif begintime<=night2 - 只用判 断 开始 日期在晚上beginsethour=datediff(minute,begintime,endtime)endendendset hour=ROUND(hour/60,1)-统计请假的小时set Shour=hour+day select Shour as 小时print morring1 print morring2 print afternoon1 print afternoon2 print night1print night2print -分钟-统计print begintime print endtime print print hour print Shour