[求助]如何请假时间段分解生成一天一条记录。
原始数据
员工ID 员工名 假别 请假开始时间 请假结束时间
001 张三 事假 2013-10-16 08:00 2013-10-20 17:20
前提：
1.上班时间是08:00 下班时间是17:20
2.不需要考虑时间段的假期，每天生成一条记录即可
结果：
员工ID 员工名 假别 请假开始时间 请假结束时间
001 张三 事假 2013-10-16 08:00 2013-10-16 17:20
001 张三 事假 2013-10-17 08:00 2013-10-17 17:20
001 张三 事假 2013-10-18 08:00 2013-10-18 17:20
001 张三 事假 2013-10-19 08:00 2013-10-19 17:20
001 张三 事假 2013-10-20 08:00 2013-10-20 17:20
[解决办法]
spt_values
[解决办法]
create table #tb(员工ID varchar(10),员工名 varchar(10),
假别 varchar(10),请假开始时间 datetime,请假结束时间 datetime)
insert into #tb
values('001','张三','事假','2013-10-16 08:00','2013-10-20 17:20')
select distinct a.员工ID,a.员工名,a.假别, dateadd(day,b.number,a.请假开始时间) as 请假开始时间,
convert(varchar(11),dateadd(day,b.number,a.请假开始时间),120)+convert(varchar(8),a.请假结束时间,108) as 请假结束时间
from #tb a,master..spt_values b
where b.number between 0 and 4
/*
员工ID员工名假别请假开始时间请假结束时间
001张三事假2013-10-16 08:00:00.0002013-10-16 17:20:00
001张三事假2013-10-17 08:00:00.0002013-10-17 17:20:00
001张三事假2013-10-18 08:00:00.0002013-10-18 17:20:00
001张三事假2013-10-19 08:00:00.0002013-10-19 17:20:00
001张三事假2013-10-20 08:00:00.0002013-10-20 17:20:00
*/
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
CREATE TABLE tb([员工ID] VARCHAR(5),[员工名] NVARCHAR(5),[假别] NVARCHAR(40),[请假开始时间] VARCHAR(16),[请假结束时间] VARCHAR(16))
INSERT INTO tb([员工ID],[员工名],[假别],[请假开始时间],[请假结束时间])
SELECT '001','张三','事假','2013-10-16 08:00','2013-10-20 17:20'
declare @time1 varchar(6),@time2 varchar(6)
set @time1=' 08:00'
set @time2=' 17:20'
select b.[员工ID],b.[员工名],b.[假别]
,convert(varchar(10),dateadd(dd,a.number,b.[请假开始时间]),120)+@time1 as '请假开始时间'
,convert(varchar(10),dateadd(dd,a.number,b.[请假开始时间]),120)+@time2 as '请假结束时间'
from master..spt_values a with(nolock),tb b with(nolock)
where a.type='P' and a.number>=1
and a.number<=DATEDIFF(DD,b.[请假开始时间],b.[请假结束时间])
DROP TABLE tb
(1 行受影响)
员工ID 员工名 假别 请假开始时间 请假结束时间
----- ----- ---------------------------------------- ---------------- ----------------
001 张三 事假 2013-10-17 08:00 2013-10-17 17:20
001 张三 事假 2013-10-18 08:00 2013-10-18 17:20
001 张三 事假 2013-10-19 08:00 2013-10-19 17:20
001 张三 事假 2013-10-20 08:00 2013-10-20 17:20
(4 行受影响)
create table #tb(员工ID varchar(10),员工名 varchar(10),
假别 varchar(10),请假开始时间 datetime,请假结束时间 datetime)
insert into #tb
select '001','张三','事假','2013-10-16 08:00','2013-10-20 17:20'
union all select '002','李四','事假','2013-10-14 15:30','2013-10-17 17:20'
union all select '003','王五','事假','2013-10-17 08:00','2013-10-19 10:30'
select distinct a.员工ID,a.员工名,a.假别, dateadd(day,b.number,a.请假开始时间) as 请假开始时间,
convert(varchar(11),dateadd(day,b.number,a.请假开始时间),120)+convert(varchar(8),a.请假结束时间,108) as 请假结束时间
from #tb a
left join master..spt_values b
on b.number between 0 and DATEDIFF(day,请假开始时间,请假结束时间)
order by a.员工ID,4
/*
员工ID员工名假别请假开始时间请假结束时间
001张三事假2013-10-16 08:00:00.0002013-10-16 17:20:00
001张三事假2013-10-17 08:00:00.0002013-10-17 17:20:00
001张三事假2013-10-18 08:00:00.0002013-10-18 17:20:00
001张三事假2013-10-19 08:00:00.0002013-10-19 17:20:00
001张三事假2013-10-20 08:00:00.0002013-10-20 17:20:00
002李四事假2013-10-14 15:30:00.0002013-10-14 17:20:00
002李四事假2013-10-15 15:30:00.0002013-10-15 17:20:00
002李四事假2013-10-16 15:30:00.0002013-10-16 17:20:00
002李四事假2013-10-17 15:30:00.0002013-10-17 17:20:00
003王五事假2013-10-17 08:00:00.0002013-10-17 10:30:00
003王五事假2013-10-18 08:00:00.0002013-10-18 10:30:00
003王五事假2013-10-19 08:00:00.0002013-10-19 10:30:00
*/
create table #tb(员工ID varchar(10),员工名 varchar(10), 假别 varchar(10),请假开始时间 datetime,请假结束时间 datetime)
insert into #tb values('001','张三','事假','2013-10-16 08:00','2013-10-20 17:20')
select 员工ID,员工名,假别,DATEADD(day,d.number-1,请假开始时间),DATEADD(day,(d.number-(datediff(DAY,请假开始时间,请假结束时间)+1)),请假结束时间)
from #tb c
right join
(
select number from (
select ((datediff(DAY,请假开始时间,请假结束时间))+1) as 'qday' from #tb) a
left join
(
select (number+1)as number from master.dbo.spt_values where status=0 and type='p') b
on number<=qday) d
on d.number<>0
--drop table tb
create table tb(
员工ID varchar(10),
员工名 varchar(20),
假别 varchar(10),
请假开始时间 datetime,
请假结束时间 datetime
)
insert into tb
select '001','张三','事假','2013-10-16 08:00','2013-10-20 17:20' union all
select '002','李四','事假','2013-10-14 15:30','2013-10-17 17:20' union all
select '003','王五','事假','2013-10-17 08:00','2013-10-19 10:30'
--用递归来生成序列
;with t
as
(
select 0 as number
union all
select number + 1 as number
from t
where t.number < 1000
)
select tb.员工ID,
tb.员工名,
tb.假别,
--请假时间在下午,时间在'13:30' and '17:20',请假时间在上午,那么时间在'08:00' and '12:30'
case when convert(varchar(5),tb.请假开始时间,114) between '13:30' and '17:20'
and t.number = 0
then convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'13:30'
when convert(varchar(5),tb.请假结束时间,114) between '08:00' and '12:30'
then convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'08:30'
else convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'08:30'
end as 请假开始时间,
case when convert(varchar(5),tb.请假开始时间,114) between '13:30' and '17:20'
then convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'17:20'
when convert(varchar(5),tb.请假结束时间,114) between '08:00' and '12:30'
and t.number = datediff(day,请假开始时间,请假结束时间)
then convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'12:30'
else convert(varchar(11),dateadd(day,t.number,tb.请假开始时间),120)+'17:20'
end as 请假结束时间
from tb
inner join t
on datediff(day,请假开始时间,请假结束时间) >= t.number
order by 员工ID,请假开始时间
option(maxrecursion 1000)
/*
员工ID 员工名 假别 请假开始时间 请假结束时间
---------- -------------------- ---------- ---------------- ----------------
001 张三 事假 2013-10-16 08:30 2013-10-16 17:20
001 张三 事假 2013-10-17 08:30 2013-10-17 17:20
001 张三 事假 2013-10-18 08:30 2013-10-18 17:20
001 张三 事假 2013-10-19 08:30 2013-10-19 17:20
001 张三 事假 2013-10-20 08:30 2013-10-20 17:20
002 李四 事假 2013-10-14 13:30 2013-10-14 17:20
002 李四 事假 2013-10-15 08:30 2013-10-15 17:20
002 李四 事假 2013-10-16 08:30 2013-10-16 17:20
002 李四 事假 2013-10-17 08:30 2013-10-17 17:20
003 王五 事假 2013-10-17 08:30 2013-10-17 17:20
003 王五 事假 2013-10-18 08:30 2013-10-18 17:20
003 王五 事假 2013-10-19 08:30 2013-10-19 12:30
(12 行受影响)
*/
