跨月的时段如何计算员工的请假天数

Question

本帖最后由 chirea 于 2011-11-18 22:04:04 编辑

Answer 1

看了一下,没看懂,友情 bang ding 了.

Answer 2

晕，你的#A表的数据重复了,难怪数据对不上 /*'001','2011-11-04 00:00:00.000'*/少了这段日期排班
/*
insert into #请假表 values('001','2011-11-29 08:43:00.000','2011-12-03 08:00:00.000',2,null)
*/结果没计算出/* 15.5 */参照以下计算方法
set nocount on;
--测试数据
if OBJECT_ID('Tempdb..#A') is not null
drop table #A
go
create table #A(员工编号 nvarchar(50) ,日期 datetime,  第一班上班时间 datetime,第一班下班时间 datetime,第二班上班时间 datetime,第二班下班时间 datetime) --班次表
insert into #A values('001','2011-11-01 00:00:00.000','1900-01-01 20:00:00.000','1900-01-01 08:00:00.000',NULL,NULL)
insert into #A values('001','2011-11-02 00:00:00.000','1900-01-01 20:00:00.000','1900-01-01 08:00:00.000',NULL,NULL)
insert into #A values('001','2011-11-03 00:00:00.000','1900-01-01 20:00:00.000','1900-01-01 08:00:00.000',NULL,NULL)
insert into #A values('001','2011-11-04 00:00:00.000','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 13:30:00.000','1900-01-01 15:30:00.000')
--insert into #A values('001','2011-11-04 00:00:00.000','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 13:30:00.000','1900-01-01 15:30:00.000')
--insert into #A values('001','2011-11-04 00:00:00.000','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 13:30:00.000','1900-01-01 15:30:00.000')
go
if OBJECT_ID('Tempdb..#请假表') is not null
drop table #请假表
go
create table #请假表(员工编号 varchar(30),请假起始时间 datetime, 请假结束时间 datetime,请假类别 int,请假小时 decimal(4,2))
insert into #请假表 values('001','2011-10-29 08:00:00.000','2011-11-02 14:00:00.000',1,null)
insert into #请假表 values('001','2011-11-03 13:30:00.000','2011-11-04 14:00:00.000',1,null)
insert into #请假表 values('001','2011-11-29 08:43:00.000','2011-12-03 08:00:00.000',2,null)--算出员工请假类型的请假小时数
/*
想要的结果
员工编号     1           2
-------    -------     ------
001        28.5        15.5s
*/
declare @dt datetime
set @dt='2011-11-01';with b1
as
(
select 员工编号,日期,
第一班上班时间,
第一班下班时间=case when 第一班下班时间<第一班上班时间 then 第一班下班时间+1 else 第一班下班时间 end,
第二班上班时间=case when 第二班上班时间<第一班上班时间 or  第二班上班时间<第一班下班时间 then 第二班上班时间+1 else 第二班上班时间 end,
第二班下班时间=case when 第二班下班时间<第一班上班时间 or  第二班下班时间<第一班下班时间 or 第二班下班时间<第二班上班时间 then 第二班下班时间+1 else 第二班下班时间 end
from #A
where datediff(m,日期,@dt)=0
),b2
as
(
select 员工编号,日期,OnShift=第一班上班时间,offShift=第一班下班时间,ShiftType=1 from b1 --ShiftType用于区分班段,便于查看理解
union all
select 员工编号,日期,OnShift=第二班上班时间,offShift=第二班下班时间,ShiftType=2 from b1 where 第二班上班时间 is not null
)
,b3
as
(
select 
a.员工编号,a.日期,b.请假类别,
a.ShiftType,
case when a.日期+a.OnShift>b.请假起始时间 then a.OnShift else b.请假起始时间-a.日期  end as OnShift,
case when a.日期+a.OffShift<b.请假结束时间 then a.OffShift else b.请假结束时间-a.日期 end as OffShift
from b2 as a
inner join #请假表 as b on a.员工编号=b.员工编号
where a.日期+a.offShift>b.请假起始时间 and b.请假结束时间>a.日期+a.OnShift)
select 员工编号,请假类别,
sum(
DATEDIFF(n,OnShift,OffShift)/60+(DATEDIFF(n,OnShift,OffShift)%60)/30*0.5
) as 小时from b3
group by 员工编号,请假类别/*
员工编号 请假类别 小时
001 1 28.5
*/