看看这样可以不:
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH--结果编号 会议时间 会议室编号 会议室名称 会议室开始时间 会议室结束时间 状态
1 2013-12-23 1 第一会议室 13:00 15:00 空闲
2 2013-12-26 2 第二会议室 9:00 10:00 空闲
3 2013-12-28 3 第三会议室 9:00 11:00 空闲
4 2013-12-29 4 第四会议室 9:00 11:00 空闲
5 2013-12-30 5 第五会议室 9:00 11:00 空闲
6 2013-12-30 6 第六会议室 16:00 20:00 使用
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH--结果编号 会议时间 会议室编号 会议室名称 会议室开始时间 会议室结束时间 状态
1 2013-12-23 1 第一会议室 13:00 15:00 空闲
2 2013-12-26 2 第二会议室 9:00 10:00 空闲
3 2013-12-28 3 第三会议室 9:00 11:00 空闲
4 2013-12-29 4 第四会议室 9:00 11:00 空闲
5 2013-12-30 5 第五会议室 9:00 11:00 空闲
6 2013-12-30 6 第六会议室 16:00 20:00 使用
create table #TH(
[编号] int null,
[会议时间] varchar(10) null,
[会议室编号] int null,
[会议室名称] varchar(50) null,
[会议室开始时间] varchar(20) null,
[会议室结束时间] varchar(20) null
)insert into #TH
select 1,'2013-12-23',1,'第一会议室','13:00','15:00' union
select 2,'2013-12-26',2,'第二会议室','9:00','10:00' union
select 3,'2013-12-28',3,'第三会议室','9:00','11:00' union
select 4,'2013-12-29',4,'第四会议室','9:00','11:00' union
select 5,'2013-12-30',5,'第五会议室','9:00','11:00' union
select 6,'2013-12-30',6,'第六会议室','16:00','20:00' --测试用
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH--第二种结果
select [会议室名称],
isnull([2013-12-23],'空闲') [2013-12-23],
isnull([2013-12-26],'空闲') [2013-12-26],
isnull([2013-12-28],'空闲') [2013-12-28],
isnull([2013-12-29],'空闲') [2013-12-29],
isnull([2013-12-30],'空闲') [2013-12-30]
from
(
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH
) a
pivot (max([状态]) for [会议时间] in ([2013-12-23],[2013-12-26],[2013-12-28],[2013-12-29],[2013-12-30])) x
这个不是最后要的结果啊 额 还是谢了大神啊这样呢:select [编号],[会议室编号],[会议室名称],[会议室开始时间],[会议室结束时间],
isnull([2013-12-23],'空闲') [2013-12-23],
isnull([2013-12-26],'空闲') [2013-12-26],
isnull([2013-12-28],'空闲') [2013-12-28],
isnull([2013-12-29],'空闲') [2013-12-29],
isnull([2013-12-30],'空闲') [2013-12-30]
from
(
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH
) a
pivot (max([状态]) for [会议时间] in ([2013-12-23],[2013-12-26],[2013-12-28],[2013-12-29],[2013-12-30])) x
如果这个还不是,那估计就要版主出马弄了呵呵
这个不是最后要的结果啊 额 还是谢了大神啊这样呢:select [编号],[会议室编号],[会议室名称],[会议室开始时间],[会议室结束时间],
isnull([2013-12-23],'空闲') [2013-12-23],
isnull([2013-12-26],'空闲') [2013-12-26],
isnull([2013-12-28],'空闲') [2013-12-28],
isnull([2013-12-29],'空闲') [2013-12-29],
isnull([2013-12-30],'空闲') [2013-12-30]
from
(
select *,
(case when(convert(datetime,[会议时间]+' '+[会议室开始时间],121)<=GETDATE() and convert(datetime,[会议时间]+' '+[会议室结束时间],121)>GETDATE()) then '使用'
else '空闲' end ) [状态] From #TH
) a
pivot (max([状态]) for [会议时间] in ([2013-12-23],[2013-12-26],[2013-12-28],[2013-12-29],[2013-12-30])) x
如果这个还不是,那估计就要版主出马弄了呵呵呵呵 差不多了 应该能解决问题了 呵呵 谢谢大神了
--创建测试表TbMeeting
if exists(select * from sys.sysobjects where name ='TbMeeting') drop table TbMeeting;
create table TbMeeting(Id int identity(1,1),Timepoint datetime,RoomId int,RoomName varchar(20),BeginTime varchar(20),EndTime varchar(20))--往测试表TbMeeting插入数据
insert into TbMeeting([Timepoint] ,[RoomId],[RoomName],[BeginTime],[EndTime])
select '2013-12-23',1,'第一会议室','13:00','15:00'
union all
select '2013-12-26',2,'第二会议室','9:00','10:30'
union all
select '2013-12-28',3,'第三会议室','9:00','11:00'
union all
select '2013-12-29',4,'第四会议室','9:00','11:00'
union all
select '2013-12-30',5,'第五会议室','9:00','11:00'--根据要求查询,在@StartTime跟@EndTime修改时间段即可查询
declare @StartTime datetime
declare @EndTime datetime
declare @sql nvarchar(max)
declare @sql1 nvarchar(max)
declare @sql2 nvarchar(max)
set @StartTime= '2013-12-20 '
set @EndTime= '2013-12-31 '
if exists(select * from sys.sysobjects where name ='t')
begin
drop table t;
end
create table t(dDate datetime);
while @StartTime <= @EndTime
begin
insert into t select @StartTime
set @StartTime=Dateadd(day,1,@StartTime)
end
select @sql=stuff((select ',(case when date='''+date+''' and RoomName is not null then ''已占用'' else ''空闲'' end) ['+date+'] '
from (
select convert(varchar(20),dDate,23) Date, RoomName from t a left join TbMeeting b on a.dDate=b.timepoint
) a for xml path('')),1,1,'')exec('select RoomName,'+@sql+' from (select convert(varchar(20),dDate,23) Date,(case when RoomName is null then '''' else RoomName end ) RoomName from t a left join TbMeeting b on a.dDate=b.Timepoint
) a
group by RoomName,date
having len(roomname)>1')
结果截图如下:
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-31 09:35:51
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([编号] int,[会议时间] datetime,[会议室编号] int,[会议室名称] varchar(10),[会议室开始时间] time,[会议室结束时间] time)
insert [huang]
select 1,'2013-12-23',1,'第一会议室','13:00','15:00' union all
select 2,'2013-12-26',2,'第二会议室','9:00','10:00' union all
select 3,'2013-12-28',3,'第三会议室','9:00','11:00' union all
select 4,'2013-12-29',4,'第四会议室','9:00','11:00' union all
select 5,'2013-12-30',5,'第五会议室','9:00','11:00' union all
select 6,'2013-12-30',6,'第六会议室','16:00','20:00'
--------------开始查询--------------------------
go
--创建函数
--create function generateTimeV2
--(
-- @begin_date datetime,
-- @end_date datetime
--)
--returns @t table(date datetime)
--as
--begin
-- insert into @t
-- select dateadd(dd,number,@begin_date) AS date
-- from master..spt_values
-- where type='p' and dateadd(dd,number,@begin_date)<=@end_date
-- return
--END
--goIF OBJECT_ID('tempdb..#t','U')IS NOT NULL
DROP TABLE #t
SELECT * INTO #t from dbo.generateTimeV2('2013-12-20','2014-01-10')
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(CONVERT(DATE,[date]))+'=max(case when CONVERT(DATE,[date])='+quotename(CONVERT(DATE,[date]),'''')+' then ''已安排'' else ''空闲'' end)'
from huang right JOIN #t ON CONVERT(DATE,huang.[会议时间])=CONVERT(DATE,[date])
group by CONVERT(DATE,huang.[会议时间]),CONVERT(DATE,[date])
ORDER BY CONVERT(DATE,[date])
exec('select [会议室名称]'+@s+' from (select * from huang right JOIN #t ON CONVERT(DATE,huang.[会议时间])=CONVERT(DATE,[date]))t where 会议室名称 is not null group by [会议室名称]
order by case substring([会议室名称],2,1) when ''一'' then 1 when ''二'' then 2 when ''三'' then 3 when ''四'' then 4 when ''五'' then 5 when ''六'' then 6 end
')
----------------结果----------------------------
/*
会议室名称 2013-12-20 2013-12-21 2013-12-22 2013-12-23 2013-12-24 2013-12-25 2013-12-26 2013-12-27 2013-12-28 2013-12-29 2013-12-30 2013-12-31 2014-01-01 2014-01-02 2014-01-03 2014-01-04 2014-01-05 2014-01-06 2014-01-07 2014-01-08 2014-01-09 2014-01-10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
第一会议室 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲
第二会议室 空闲 空闲 空闲 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲
第三会议室 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲
第四会议室 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲
第五会议室 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲
第六会议室 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 已安排 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲 空闲*/