一个关于考勤计算的问题

Question

正常情况周一至周五每天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
请大家帮忙，高分重谢！

Answer 1

很简单 给你个思路..
你应该有张班次表
转换后就得到表A
排班日期 开始时间  结束时间 标识
2011-1-11 8:00      12:00   1
2011-1-11 12:01     14：00  0
2011-1-11 14:01    17:30    1
2011-1-12 8:00     11:00    1
假如2011-1-5是周六，1代表上班时段，0代表休息时段
将你的请假表转换成B表
22200011 2012-01-11 8:00 2012-01-11 17:30
22200011 2012-01-12 8:00 2012-01-11 12:00
22200011 2012-01-18 11:00 2012-01-18 17:30
22200011 2012-01-19 8:00 2012-01-19 17:30
22200011 2012-01-20 8:00 2012-01-20 17:30
22200011 2012-01-21 8:00 2012-01-21 11:00
根据表A和表B 你可以得到周一到周五时间内的请假 开始时间是8:00到17:30的很简单就是休假了7.5小时
开始时间在休息时间之间的，像8:00到12:30就和A表中的8:00到12:00结束时间取小 就是4小时。
开始时间是11:00到17:30 剔除掉表A中12:01到14:00的休息记录就能得到如果数据量小可以用游标计算，如果数据量大就批量来做...

Answer 2

确实，这个东东需要很多判断才可以，一个SQL搞不定，最好用存储过程处理中间数据，最后返回结果。

Answer 3

declare @hrmexcuse table 
(
人员编号 int,请假开始日期 VARCHAR(10),请假开始时间 VARCHAR(5),
请假结束日期 VARCHAR(10),请假结束时间 VARCHAR(5)
)
insert into @hrmexcuse
select 22200011,'2012-01-11' ,'08:00','2012-01-12','12:00' union all
select 22200011,'2012-01-18' ,'11:00','2012-01-21' ,'11:00' union all
select 22200011,'2012-01-30' ,'08:00','2012-01-31' ,'16:30'--按天展开,插入临时表进行处理,以免影响原始数据
SELECT  人员编号 ,
        CAST(a.请假开始日期 AS DATETIME) + b.number AS 请假开始日期 ,
--        CASE WHEN number = 0 THEN 请假开始时间
--             ELSE '8:00'
--        END 请假开始时间 ,
        请假开始时间 ,
        请假结束日期 ,
        请假结束时间 ,
        b.number ,
        1.00 AS 请假时间 ,
        0 AS 星期 ,
        0 AS 状态
INTO    #t
FROM    @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, 请假开始日期)
--如果日期段中有星期天，更新为0
UPDATE  #t
SET     请假时间 = 0 ,
        状态 = 1
WHERE   星期 = 7
--设计部分日期的开始时间
UPDATE  #t
SET     请假开始时间 = CASE WHEN number = 0 THEN 请假开始时间
                      WHEN number <> 0
                           AND 星期 <> 6 THEN '08:00'
                      WHEN number <> 0
                           AND 星期 = 6 THEN '08:30'
                 END--连续日期的话除收尾外，更新为整天时间
UPDATE  #t
SET     请假时间 = CASE 星期
                 WHEN 6 THEN 2.5
                 ELSE 7.5
               END ,
        状态 = 1
FROM    #t t
WHERE   number NOT 
IN ( SELECT MAX(number)
     FROM   #t
     WHERE  请假结束日期 = t.请假结束日期
            AND 请假结束时间 = t.请假结束时间
     UNION ALL
     SELECT 0 )
        AND 星期 <> 7UPDATE  #t
SET     请假时间 = CASE 星期
                 WHEN 6 THEN 2.5
                 ELSE 7.5
               END ,
        状态 = 1
FROM    #t t
WHERE   number NOT 
IN ( SELECT MAX(number)
     FROM   #t
     WHERE  请假结束日期 = t.请假结束日期
            AND 请假结束时间 = t.请假结束时间
     UNION ALL
     SELECT 0 )--不是最后一天的话，结束时间应该是17.30
UPDATE  #t
SET     请假结束时间 = '17:30'
FROM    #t t
WHERE   number NOT 
IN ( SELECT MAX(number)
     FROM   #t
     WHERE  请假结束日期 = t.请假结束日期
            AND 请假结束时间 = t.请假结束时间 )UPDATE  #t
SET     请假时间 = 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
               END
WHERE   状态 = 0
SELECT  人员编号 ,
        convert(VARCHAR(10),请假开始日期,120) AS  请假日期,
        请假时间 ,
        星期
FROM    #t /*
人员编号        请假日期       请假时间                                    星期
----------- ---------- --------------------------------------- -----------
22200011    2012-01-11 7.50                                    3
22200011    2012-01-12 4.00                                    4
22200011    2012-01-18 4.50                                    3
22200011    2012-01-19 7.50                                    4
22200011    2012-01-20 7.50                                    5
22200011    2012-01-21 2.50                                    6  --这天是周六，一共2.5小时，你给出的结果中的3是不对的
22200011    2012-01-30 7.50                                    1
22200011    2012-01-31 6.50                                    2
*/
DROP TABLE #t

Answer 4

declare @hrmexcuse table 
(
人员编号 int,请假开始日期 VARCHAR(10),请假开始时间 VARCHAR(5),
请假结束日期 VARCHAR(10),请假结束时间 VARCHAR(5)
)
insert into @hrmexcuse
select 22200011,'2012-01-11' ,'08:00','2012-01-12','12:00' union all
select 22200011,'2012-01-18' ,'11:00','2012-01-21' ,'11:00' union all
select 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    #t
FROM    @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, 请假开始日期)
--如果日期段中有星期天，更新为0
UPDATE  #t  SET 请假时间 = 0 ,状态 = 1 WHERE 星期 = 7
--设计部分日期的开始时间
UPDATE  #t
SET     请假开始时间 = CASE WHEN number = 0 THEN 请假开始时间
                      WHEN number <> 0
                           AND 星期 <> 6 THEN '08:00'
                      WHEN number <> 0
                           AND 星期 = 6 THEN '08:30'
                 END
--连续日期的话除收尾外，更新为整天时间
UPDATE  #t
SET  请假时间 = CASE 星期 WHEN 6 THEN 2.5 ELSE 7.5 END ,状态 = 1
FROM    #t t WHERE   number NOT 
IN ( SELECT MAX(number) FROM #t WHERE  请假结束日期 = t.请假结束日期
            AND 请假结束时间 = t.请假结束时间
     UNION ALL SELECT 0 ) AND 星期 <> 7
--不是最后一天的话，结束时间应该是17.30
UPDATE  #t SET  请假结束时间 = '17:30'
FROM  #t t
WHERE number NOT 
IN ( SELECT MAX(number)
     FROM   #t
     WHERE  请假结束日期 = t.请假结束日期 AND 请假结束时间 = t.请假结束时间 )UPDATE  #t
SET     请假时间 = 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
               END
WHERE   状态 = 0
SELECT  人员编号,convert(VARCHAR(10),请假开始日期,120) AS 请假日期,请假时间 ,星期 FROM #t /*
人员编号        请假日期       请假时间                                    星期
----------- ---------- --------------------------------------- -----------
22200011    2012-01-11 7.50                                    3
22200011    2012-01-12 4.00                                    4
22200011    2012-01-18 4.50                                    3
22200011    2012-01-19 7.50                                    4
22200011    2012-01-20 7.50                                    5
22200011    2012-01-21 2.50                                    6
22200011    2012-01-30 7.50                                    1
22200011    2012-01-31 6.50                                    2
*/
DROP TABLE #t

Answer 5

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'
go
select 人员编号,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./60
end)t
from(
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)te
from 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' t4
from 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.500000
22200011   2012-01-12  4.000000
22200011   2012-01-18  4.500000
22200011   2012-01-19  7.500000
22200011   2012-01-20  7.500000
22200011   2012-01-21  3.000000
22200011   2012-01-30  7.500000
22200011   2012-01-31  6.500000(8 行受影响)*/
go
drop table hrmexcuse