34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
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')
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.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
*/