一个关于考勤计算的有关问题

一个关于考勤计算的问题
正常情况周一至周五每天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