SQL考勤请假时间计算

1、SQL 考勤请假时间计算推荐资料自学网址:http:/ 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 asvarchar(800) declare begin as

2、 datetime declare end asdatetime declare report as datetime set begin=begindateset 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)-早上下班时间dec

3、lare 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(varc

4、har(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 andyears=year(begin) and months=month(begin)select

5、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(varchar(100),

6、晚上下班时间 , 24)from N- 作息时间设置 where 部门别 =Depno and 年度=year(begin) and 月份 =month(begin)if isnull(night1,)= or isnull(night2,)= or night1=00:00:00or 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) selectcount(*) AS KqDayfrom HN_KqDaySet

8、 wherebranchid=+Depno+ and years=year(+begintime+) andmonths=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=0end-0 请假setday=datediff(minute,morri

9、ng1,morring2)+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- 请假开始时间在上

10、午begin - 请假截止时间判断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=d

11、atediff(minute,begintime,morring2)+datediff(minute,afternoon1,afternoon2)+datediff(minute,night1,endtime) printhourendendelse if begintime<=afternoon2- 请假开始时间在下午begin- 对请假截止时间判断if endtime<=afternoon2beginset hour=datediff(minute,begintime,endtime)end else if endtime<=night2 begi

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

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

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