一个比较难的日期分拆，完全没有思路。

[beyondjay]

现在数据库里记录会议的开始时间是用starttime='2007-8-7 11:30:00'和endtime='2007-8-7 14:30:00'，现在有个要求是能跑出来每个时间段的会议室利用情况。
比如 11:00 11:30 12:00 12:00 ..............
Room1 Y Y N N ..............
不知道这个情况能用sql 解决吗？
好像挺复杂的。

[dawugui]

create table tb(time datetime , [user] varchar(10))
declare @starttime as datetime
set @starttime = '2007-8-7 11:30:00'

declare @endtime as datetime
set @endtime = '2007-8-7 14:30:00'

declare @inttime as datetime
set @inttime = convert(varchar(10),@starttime,120) + ' 00:00:00'

declare @int as int
set @int = 1

while @int < 48
begin
set @inttime = dateadd(minute,30,@inttime)
if @inttime >= @starttime and @inttime <= @endtime
insert into tb values(@inttime , 'Y')
else
insert into tb values(@inttime , 'N')
set @int = @int + 1
end
select * from tb
drop table tb

/*
time user
------------------------------------------------------ ----------
2007-08-07 00:30:00.000 N
2007-08-07 01:00:00.000 N
2007-08-07 01:30:00.000 N
2007-08-07 02:00:00.000 N
2007-08-07 02:30:00.000 N
2007-08-07 03:00:00.000 N
2007-08-07 03:30:00.000 N
2007-08-07 04:00:00.000 N
2007-08-07 04:30:00.000 N
2007-08-07 05:00:00.000 N
2007-08-07 05:30:00.000 N
2007-08-07 06:00:00.000 N
2007-08-07 06:30:00.000 N
2007-08-07 07:00:00.000 N
2007-08-07 07:30:00.000 N
2007-08-07 08:00:00.000 N
2007-08-07 08:30:00.000 N
2007-08-07 09:00:00.000 N
2007-08-07 09:30:00.000 N
2007-08-07 10:00:00.000 N
2007-08-07 10:30:00.000 N
2007-08-07 11:00:00.000 N
2007-08-07 11:30:00.000 Y
2007-08-07 12:00:00.000 Y
2007-08-07 12:30:00.000 Y
2007-08-07 13:00:00.000 Y
2007-08-07 13:30:00.000 Y
2007-08-07 14:00:00.000 Y
2007-08-07 14:30:00.000 Y
2007-08-07 15:00:00.000 N
2007-08-07 15:30:00.000 N
2007-08-07 16:00:00.000 N
2007-08-07 16:30:00.000 N
2007-08-07 17:00:00.000 N
2007-08-07 17:30:00.000 N
2007-08-07 18:00:00.000 N
2007-08-07 18:30:00.000 N
2007-08-07 19:00:00.000 N
2007-08-07 19:30:00.000 N
2007-08-07 20:00:00.000 N
2007-08-07 20:30:00.000 N
2007-08-07 21:00:00.000 N
2007-08-07 21:30:00.000 N
2007-08-07 22:00:00.000 N
2007-08-07 22:30:00.000 N
2007-08-07 23:00:00.000 N
2007-08-07 23:30:00.000 N

（所影响的行数为 47 行）

*/

[beyondjay]

牛人!太牛了！

[livan1038]

牛人，學習

[dobear_0922]

/*------------------------
if object_id(N'tb', 'U') is not null
drop table tb
GO
create table tb(nId int identity(1,1) primary key, room nvarchar(16), starttime datetime, endtime datetime)
go

insert tb values('room1','2007-08-28 12:00:00','2007-08-28 13:00:00');
insert tb values('room2','2007-08-28 12:00:00','2007-08-28 12:20:00');
insert tb values('room3','2007-08-28 10:00:00','2007-08-28 13:00:00');
insert tb values('room4','2007-08-28 13:00:00','2007-08-28 15:00:00');
insert tb values('room4','2007-08-28 08:00:00','2007-08-28 10:00:00');

select room,
'11:00'=(case when exists(select 1 from tb where convert(varchar(10),getdate(),120)+' 11:00:00' between starttime and endtime and tb.room=t.room) then 'N' else 'Y' end),
'11:30'=(case when exists(select 1 from tb where convert(varchar(10),getdate(),120)+' 11:30:00' between starttime and endtime and tb.room=t.room) then 'N' else 'Y' end),
'12:00'=(case when exists(select 1 from tb where convert(varchar(10),getdate(),120)+' 12:00:00' between starttime and endtime and tb.room=t.room) then 'N' else 'Y' end),
'12:30'=(case when exists(select 1 from tb where convert(varchar(10),getdate(),120)+' 12:30:00' between starttime and endtime and tb.room=t.room) then 'N' else 'Y' end)
from tb as t
group by room
order by room
------------------------*/

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
room 11:00 11:30 12:00 12:30
---------------- ----- ----- ----- -----
room1 Y Y N N
room2 Y Y N Y
room3 N N N N
room4 Y Y Y Y

(4 row(s) affected)