问一个比较难的SQL

xubingjingme 2009-07-17 10:20:41

原表
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

...全文

65 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

yinchuanwang 2009-07-20

打赏
举报



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_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 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_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 = 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_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 = 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_end



from 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

*/

yinchuanwang 2009-07-20

打赏
举报

[Quote=引用 11 楼 yinchuanwang 的回复:]
SQL codecreatetable TAA
(
t_ghint,
t_typechar(3),
t_begindatetime,
t_enddatetime
)insertinto TAAvalues
(440,'001','2009-07-07 15:00:00','2009-07-07 17:00:00'),
(440,'002','2008-07-10 14:00:00','2008-0¡
[/Quote]

晕，考虑复杂了。把中午 12：00-13：00 一个小时吃饭，也给排出了下。

不过没考虑周末。

gzzwind 2009-07-19

打赏
举报

帮顶下啦

CSIBM 2009-07-19

打赏
举报

错了，题没看清，sorry

CSIBM 2009-07-19

打赏
举报

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

playwarcraft 2009-07-17

打赏
举报

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

lg3605119 2009-07-17

打赏
举报



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

*/

SQL77 2009-07-17

打赏
举报

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

jwdream2008 2009-07-17

打赏
举报

帮顶！

meheartfly 2009-07-17

打赏
举报

看看你的结贴率，不到30%，懒得搭理你

ChinaJiaBing 2009-07-17

打赏
举报





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 行受影响)