问一个比较难的SQL

Question

原表
gh         type            begin_time                           end_time  
440         001        2009-07-07 15:00:00                2009-07-07 17:00:00 
440         002        2008-07-10 14:00:00                2008-07-12 15:00:00 
我想转化为： gh             type                     time                  
440              001               2009-07-07 15:00:00 
440              001               2009-07-07 17:00:00 
440              002               2008-07-10 14:00:00 
440              002               2008-07-10 17:00:00 
440              002               2008-07-11 08:30:00 
440              002               2008-07-11 17:00:00 
440              002               2008-07-12 08:30:00 
440              002               2008-07-12 15:00:00 
因为每天上班时间是 08：30 ~~~~17：00
如果有连续请假的，我需要把记录拆分，比如type=‘002’的记录,不知道如何写SQL     

Answer 1

动态SQL,UNION ALL看能不能实现?头晕想不到

Answer 2

declare @T table(gh int,type  varchar(10),begin_time datetime,end_time  datetime)
insert into @T
SELECT 440 ,       '001' ,       '2009-07-07 15:00:00'  ,              '2009-07-07 17:00:00' UNION ALL
SELECT 440 ,       '002'  ,      '2008-07-10 14:00:00'   ,             '2008-07-12 15:00:00' 
SELECT GH,TYPE,BEGIN_TIME AS TIME
FROM
(
SELECT A.GH,
       A.TYPE,
       [BEGIN_TIME] =  CASE WHEN A.BEGIN_TIME = DATEADD(DAY,NUMBER,A.BEGIN_TIME) 
                          THEN BEGIN_TIME ELSE CAST(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120)+' 08:30:00' AS DATETIME) END,
       [END_TIME] = CASE WHEN CONVERT(VARCHAR(10),A.END_TIME,120) =  CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120) 
                         THEN END_TIME ELSE CAST(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120)+' 17:00:00' AS DATETIME) END
FROM @T A ,MASTER..SPT_VALUES B 
WHERE B.TYPE = 'P' 
   AND CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120) <= CONVERT(VARCHAR(10),A.END_TIME,120)
)T
UNION ALL
SELECT GH,TYPE,END_TIME AS TIME
FROM
(
SELECT A.GH,
       A.TYPE,
       [BEGIN_TIME] =  CASE WHEN A.BEGIN_TIME = DATEADD(DAY,NUMBER,A.BEGIN_TIME) 
                          THEN BEGIN_TIME ELSE CAST(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120)+' 08:30:00' AS DATETIME) END,
       [END_TIME] = CASE WHEN CONVERT(VARCHAR(10),A.END_TIME,120) =  CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120) 
                         THEN END_TIME ELSE CAST(CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120)+' 17:00:00' AS DATETIME) END
FROM @T A ,MASTER..SPT_VALUES B 
WHERE B.TYPE = 'P' 
   AND CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,A.BEGIN_TIME),120) <= CONVERT(VARCHAR(10),A.END_TIME,120)
)T
ORDER BY TYPE,TIME /*
440 001 2009-07-07 15:00:00.000
440 001 2009-07-07 17:00:00.000
440 002 2008-07-10 14:00:00.000
440 002 2008-07-10 17:00:00.000
440 002 2008-07-11 08:30:00.000
440 002 2008-07-11 17:00:00.000
440 002 2008-07-12 08:30:00.000
440 002 2008-07-12 15:00:00.000
*/

Answer 3

似乎还要考虑 周末 等休假哦~~~
最好是有一个working time 的table。，。。

Answer 4

select 
  gh,type,begin-time
from 
  table
union all
select 
  gh,type,end-time
from 
  table
order by
  gh,type

Answer 5

declare @tb table (gh int,type int ,begin_time datetime ,end_time datetime)
insert into @tb select 440,1,'2009-7-7 15:00:00','2009-7-7 17:00:00'
      union all select 440,2,'2009-7-10 14:00:00','2009-7-12 15:00:00'
select top 100 ID=identity(int ,0,1) ,a.name into # from dbo.sysobjects a ,dbo.syscolumns b
;with China as
( 
select  gh,type,begin_time= case when DATEADD(DD,id, convert(nvarchar(10),begin_time,120))=
  convert(nvarchar(10),begin_time,120) then begin_time else DATEADD(DD,id, convert(nvarchar(10),begin_time,120))+'8:30:00' 
  end,end_time =case when DATEADD(DD,id, convert(nvarchar(10),end_time,120))=
  convert(nvarchar(10),end_time,120) then end_time else DATEADD(DD,-id, convert(nvarchar(10),end_time,120))+'17:00:00' 
                 end from @tb a join # b on DATEADD(dd,id, CONVERT(nvarchar(10),begin_time,120))
                             <= CONVERT(nvarchar(10),end_time,120)
)
select gh,type,时间=begin_time from (
 select gh,TYPE,begin_time from china
 union all
 select gh,TYPE,end_time from china) tb  order by 时间
go
drop table #(100 行受影响)
gh          type        时间
----------- ----------- -----------------------
440         1           2009-07-07 15:00:00.000
440         1           2009-07-07 17:00:00.000
440         2           2009-07-10 14:00:00.000
440         2           2009-07-10 17:00:00.000
440         2           2009-07-11 08:30:00.000
440         2           2009-07-11 17:00:00.000
440         2           2009-07-12 08:30:00.000
440         2           2009-07-12 15:00:00.000(8 行受影响)

Answer 6

这个简单
一条语句搞定
insert into yourTable
select gh,type,end_time,'', from yourtable

Answer 7

create table TAA
(
t_gh int,
t_type char(3),
t_begin datetime,
t_end datetime
)insert into TAA values
(440, '001', '2009-07-07 15:00:00', '2009-07-07 17:00:00'),
(440, '002', '2008-07-10 14:00:00', '2008-07-12 15:00:00'),
(440, '003', '2006-07-07 09:00:00', '2006-07-07 17:00:00'),
(440, '004', '2006-07-10 10:00:00', '2006-07-12 15:00:00'),
(440, '005', '2006-08-10 14:00:00', '2006-08-11 11:00:00')with 
s1 as (select 1 as c UNION ALL SELECT 1),
s2 as (select 1 as c from s1 as A, s1 as B),
s3 as (select 1 as c from s2 as A, s2 as B),
s4 as (select 1 as c from s3 as A, s3 as B),
Nums as (select ROW_NUMBER() over(Order by c) as n from s4)
select t_gh, t_type,case 
when n = 1
then t_begin
when n > 1 and (DAY(t_begin) = DAY(t_end) )
then CONVERT(datetime,convert(date,t_begin)) + '13:00:00.000'
when n > 1  
and (DAY(t_begin) < DAY(t_end))
and (n%2 = 0) 
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime, convert(date,dateadd(day,n/2,t_begin)))  + '08:30:00.000'
when  n > 1  
and (DAY(t_begin) < DAY(t_end))
and (n%2 = 1) 
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime, convert(date,dateadd(day,(n-1)/2,t_begin)))  + '13:00:00.000'


when n > 2  
and (DAY(t_begin) < DAY(t_end))
and (n%2 = 0) 
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime, convert(date,dateadd(day,(n-2)/2,t_begin)))  + '13:00:00.000'when n > 2  
and (DAY(t_begin) < DAY(t_end))
and (n%2 = 1) 
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime, convert(date,dateadd(day,(n-1)/2,t_begin)))  + '08:30:00.000'

when n = 2  
and (DAY(t_begin) < DAY(t_end))
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime, convert(date,t_begin))  + '13:00:00.000'

else NULL
END AS t_begin,case 
when n = 1
and day(t_begin) = day(t_end)
and (t_end < (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
then t_end
when n = 1
and DAY(t_begin) = DAY(t_end)
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'
when n = 1
and day(t_begin) = day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then t_end
when n = 2
and DAY(t_begin) = DAY(t_end)
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
then t_end
when n = 1
and day(t_begin) < day(t_end)
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'
when n = 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
then CONVERT(datetime,convert(date,t_begin)) + '17:00:00.000'
when n > 1
and day(t_begin) < day(t_end)
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
then CONVERT(datetime,convert(date,dateadd(day,(n-1)/2,t_begin))) + '12:00:00.000'
when  n > 1
and day(t_begin) < day(t_end)
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
and (n/2 -1) < datediff(day,t_begin, t_end)
then CONVERT(datetime,convert(date,dateadd(day,n/2,t_begin))) + '17:00:00.000' 

when  n > 1
and day(t_begin) < day(t_end)
and (t_begin < (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
and (n/2 - 1) = datediff(day,t_begin, t_end)
then t_endwhen n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
then CONVERT(datetime,convert(date,dateadd(day,n/2,t_begin))) + '12:00:00.000'

when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
and (n-1)/2 < datediff(day,t_begin, t_end)
then CONVERT(datetime,convert(date,dateadd(day,n/2,t_begin))) + '17:00:00.000'when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
and (n-1)/2 = datediff(day,t_begin, t_end)
then t_endwhen n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end < (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
then CONVERT(datetime,convert(date,dateadd(day,(n-1)/2,t_begin))) + '17:00:00.000'when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end < (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
and n/2 < datediff(day,t_begin, t_end)
then CONVERT(datetime,convert(date,dateadd(day,n/2,t_begin))) + '12:00:00.000'

when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end < (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
and n/2 = datediff(day,t_begin, t_end)
then t_end

when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 0
then CONVERT(datetime,convert(date,dateadd(day,n/2,t_begin))) + '12:00:00.000'

when n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
and (n-1)/2 = datediff(day,t_begin,t_end)
then t_endwhen n > 1
and day(t_begin) < day(t_end)
and (t_begin > (CONVERT(datetime,convert(date,t_begin)) + '12:00:00.000'))
and (t_end > (CONVERT(datetime,convert(date,t_end)) + '12:00:00.000'))
and n%2 = 1
and (n-1)/2 < datediff(day,t_begin,t_end)
then CONVERT(datetime,convert(date,dateadd(day,(n-1)/2,t_begin))) + '17:00:00.000' else null
end
as t_endfrom TAA t1 JOIN Nums 
on Nums.n <= 
(DATEDIFF(DAY,t1.t_begin, t1.t_end)*2 + 
(case 
when DAY(t_begin) = DAY(t_end) and 
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') > t_begin and 
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') < t_end
then 2
when DAY(t_begin) = DAY(t_end) and 
(
(
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') > t_begin and 
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') > t_end 
) or
(
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') < t_begin and 
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') < t_end 
)
)
then 1
when DAY(t_begin) < DAY(t_end) and
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') > t_begin and 
(convert(datetime,CONVERT(date,t_end)) + '12:00:00.000') < t_end
then 2
when DAY(t_begin) < DAY(t_end) and
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') > t_begin and 
(convert(datetime,CONVERT(date,t_end)) + '12:00:00.000') > t_end
then 1
when DAY(t_begin) < DAY(t_end) and
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') < t_begin and 
(convert(datetime,CONVERT(date,t_end)) + '12:00:00.000') < t_end
then 1
when DAY(t_begin) < DAY(t_end) and
(convert(datetime,CONVERT(date,t_begin)) + '12:00:00.000') < t_begin and 
(convert(datetime,CONVERT(date,t_end)) + '12:00:00.000') > t_end
then 0
else 0
end)
)
order by t_type, t_gh, n/*
t_gh t_type t_begin t_end
440 001 2009-07-07 15:00:00.000 2009-07-07 17:00:00.000
440 002 2008-07-10 14:00:00.000 2008-07-10 17:00:00.000
440 002 2008-07-11 08:30:00.000 2008-07-11 12:00:00.000
440 002 2008-07-11 13:00:00.000 2008-07-11 17:00:00.000
440 002 2008-07-12 08:30:00.000 2008-07-12 12:00:00.000
440 002 2008-07-12 13:00:00.000 2008-07-12 15:00:00.000
440 003 2006-07-07 09:00:00.000 2006-07-07 12:00:00.000
440 003 2006-07-07 13:00:00.000 2006-07-07 17:00:00.000
440 004 2006-07-10 10:00:00.000 2006-07-10 12:00:00.000
440 004 2006-07-10 13:00:00.000 2006-07-11 17:00:00.000
440 004 2006-07-11 08:30:00.000 2006-07-11 12:00:00.000
440 004 2006-07-11 13:00:00.000 2006-07-12 17:00:00.000
440 004 2006-07-12 08:30:00.000 2006-07-12 12:00:00.000
440 004 2006-07-12 13:00:00.000 2006-07-12 15:00:00.000
440 005 2006-08-10 14:00:00.000 2006-08-10 17:00:00.000
440 005 2006-08-11 08:30:00.000 2006-08-11 11:00:00.000
*/