正常情况周一至周五每天7.5小时，周六2.5小时。
周一至周五的上班时间为：上午 8：00~12：00 下午：14：00~17：30
周六上班时间为：8：30~11：00
请假记录表：
hrmexcuse
人员编号 请假开始日期 请假开始时间 请假结束日期 请假结束时间
22200011 2012-01-11 8:00 2012-01-12 12:00
22200011 2012-01-18 11:00 2012-01-21 11:00
22200011 2012-01-30 8:00 2012-01-31 16:30
结果应该显示如下：
人员编号 请假日期 请假时间
22200011 2012-01-11 7.5
22200011 2012-01-12 4
22200011 2012-01-18 4.5
22200011 2012-01-19 7.5
22200011 2012-01-20 7.5
22200011 2012-01-21 3
22200011 2012-01-30 7.5
22200011 2012-01-31 6.5
请大家帮忙，高分重谢！
------解决方案--------------------
- SQL code
declare @hrmexcuse table (人员编号 int,请假开始日期 VARCHAR(10),请假开始时间 VARCHAR(5),请假结束日期 VARCHAR(10),请假结束时间 VARCHAR(5))insert into @hrmexcuseselect 22200011,'2012-01-11' ,'08:00','2012-01-12','12:00' union allselect 22200011,'2012-01-18' ,'11:00','2012-01-21' ,'11:00' union allselect 22200011,'2012-01-30' ,'08:00','2012-01-31' ,'16:30'--分步处理相对容易一些--按天展开,插入临时表进行处理,以免影响原始数据SELECT 人员编号 ,CAST(a.请假开始日期 AS DATETIME) + b.number AS 请假开始日期 , 请假开始时间 ,请假结束日期 ,请假结束时间 , b.number ,1.00 AS 请假时间 ,0 AS 星期 ,0 AS 状态INTO #tFROM @hrmexcuse a LEFT JOIN master..spt_values b ON CAST(a.请假开始日期 AS DATETIME) + b.number <= CAST(a.请假结束日期 AS DATETIME)WHERE b.type = 'p'--设置星期一为一个星期的第一天SET DATEFIRST 1 --更新星期字段UPDATE #t SET 星期 = DATEPART(weekday, 请假开始日期)--如果日期段中有星期天，更新为0UPDATE #t SET 请假时间 = 0 ,状态 = 1 WHERE 星期 = 7--设计部分日期的开始时间UPDATE #tSET 请假开始时间 = CASE WHEN number = 0 THEN 请假开始时间 WHEN number <> 0 AND 星期 <> 6 THEN '08:00' WHEN number <> 0 AND 星期 = 6 THEN '08:30' END--连续日期的话除收尾外，更新为整天时间UPDATE #tSET 请假时间 = CASE 星期 WHEN 6 THEN 2.5 ELSE 7.5 END ,状态 = 1FROM #t t WHERE number NOT IN ( SELECT MAX(number) FROM #t WHERE 请假结束日期 = t.请假结束日期 AND 请假结束时间 = t.请假结束时间 UNION ALL SELECT 0 ) AND 星期 <> 7--不是最后一天的话，结束时间应该是17.30UPDATE #t SET 请假结束时间 = '17:30'FROM #t tWHERE number NOT IN ( SELECT MAX(number) FROM #t WHERE 请假结束日期 = t.请假结束日期 AND 请假结束时间 = t.请假结束时间 )UPDATE #tSET 请假时间 = CASE WHEN '12:00' BETWEEN 请假开始时间 AND 请假结束时间 AND '14:00' BETWEEN 请假开始时间 AND 请假结束时间 THEN ( DATEDIFF(mi, CAST(请假开始日期 + ' ' + 请假开始时间 AS DATETIME), CAST(请假开始日期 + ' ' + 请假结束时间 AS DATETIME)) - 120 ) / 60.0 ELSE ( DATEDIFF(mi, CAST(请假开始日期 + ' ' + 请假开始时间 AS DATETIME), CAST(请假开始日期 + ' ' + 请假结束时间 AS DATETIME)) ) / 60.0 ENDWHERE 状态 = 0SELECT 人员编号,convert(VARCHAR(10),请假开始日期,120) AS 请假日期,请假时间 ,星期 FROM #t /*人员编号 请假日期 请假时间 星期----------- ---------- --------------------------------------- -----------22200011 2012-01-11 7.50 322200011 2012-01-12 4.00 422200011 2012-01-18 4.50 322200011 2012-01-19 7.50 422200011 2012-01-20 7.50 522200011 2012-01-21 2.50 622200011 2012-01-30 7.50 122200011 2012-01-31 6.50 2*/DROP TABLE #t
------解决方案--------------------
- SQL code
create table hrmexcuse(人员编号 varchar(10),请假开始日期 varchar(10),请假开始时间 varchar(8),请假结束日期 varchar(10),请假结束时间 varchar(8))--如果是2005及以下,不用时间数据类型而用字符,则8:00要改为08:00,如为2008,则要改程序,以时间来计算insert into hrmexcuse select '22200011','2012-01-11','08:00','2012-01-12','12:00' insert into hrmexcuse select '22200011','2012-01-18','11:00','2012-01-21','11:00'insert into hrmexcuse select '22200011','2012-01-30','08:00','2012-01-31','16:30'goselect 人员编号,dt,(case when ts=t1 and te=t4 then 7.5 when ts>t1 and ts<t2 and te=t4 then datediff(mi,ts,t2)*1./60+3.5 when ts>=t2 and ts<=t3 and te=t4 then 3.5 when ts>t3 and te=t4 then datediff(mi,ts,t4)*1./60 when ts=t1 and te>t3 and te<t4 then datediff(mi,t3,te)*1./60+4 when ts=t1 and te>=t2 and te<=t3 then 4 when ts=t1 and te<t2 then datediff(mi,t1,te)*1./60 when ts>t1 and ts<t2 and te>t3 and te<t4 then (datediff(mi,ts,t2)+datediff(mi,t3,te))*1./60 when ts>t1 and ts<t2 and te>=t2 and te<=t3 then datediff(mi,ts,t2)*1./60 when ts>=t2 and ts<=t3 and te>t3 and te<t4 then datediff(mi,t3,te)*1./60 when ts>t1 and te<te or ts>t3 and te<t4 then datediff(mi,ts,te)*1./60end)tfrom(select a.人员编号,b.*,(case when a.请假开始日期<b.dt then '08:00' else a.请假开始时间 end)ts,(case when a.请假结束日期>b.dt then '17:30' else a.请假结束时间 end)tefrom hrmexcuse a,(select convert(varchar(11),dateadd(d,a.number,b.s),120)dt,'08:00' t1,'12:00' t2,'14:00' t3,'17:30' t4from master..spt_values a,(select min(请假开始日期)s,max(请假结束日期)e from hrmexcuse)b where a.type='p' and dateadd(d,a.number,b.s)<=b.e)b where a.请假开始日期<=b.dt and a.请假结束日期>=b.dt)a/*人员编号 dt t---------- ----------- ---------------------------------------22200011 2012-01-11 7.50000022200011 2012-01-12 4.00000022200011 2012-01-18 4.50000022200011 2012-01-19 7.50000022200011 2012-01-20 7.50000022200011 2012-01-21 3.00000022200011 2012-01-30 7.50000022200011 2012-01-31 6.500000(8 行受影响)*/godrop table hrmexcuse