--交叉表,根據(jù)優(yōu)先級(jí)取數(shù)據(jù),日期處理

create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)
insert tb select 1,'A1','未訂','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','未訂','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','未訂','10:00','11:00',1   ,5   ,null       ,null       ,1
union all select 1,'A1','裝修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2 
--union all select 1,'A1','裝修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A1','裝修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','未訂','08:00','09:00',1   ,5   ,null       ,null       ,1
union all select 1,'A2','未訂','09:00','10:00',1   ,5   ,null       ,null       ,1
union all select 1,'A2','未訂','10:00','11:00',1   ,5   ,null       ,null       ,1
--union all select 1,'A2','裝修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union all select 1,'A2','裝修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
--union all select 1,'A2','裝修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
go

/*--樓主這個(gè)問題要考慮幾個(gè)方面

	1. 取星期時(shí),set datefirst 的影響
	2. 優(yōu)先級(jí)問題
	3. qid,rid 應(yīng)該是未知的(動(dòng)態(tài)變化的)
--*/

--實(shí)現(xiàn)的存儲(chǔ)過程如下
create proc p_qry
@date smalldatetime --要查詢的日期
as
set nocount on
declare @week int,@s nvarchar(4000)
--格式化日期和得到星期
select @date=convert(char(10),@date,120)
	,@week=(@@datefirst+datepart(weekday,@date)-1)%7
	,@s=''
select id=identity(int),* into #t
from(
	select top 100 percent
		qid,rid,tagname,
		starttime=convert(char(5),starttime,108),
		endtime=convert(char(5),endtime,108)
	from tb
	where (@week between startweekday and endweekday)
		or(@date between startdate and enddate)
	order by qid,rid,starttime,d desc)a

select @s=@s+N',['+rtrim(rid)
	+N']=max(case when qid='+rtrim(qid)
	+N' and rid=N'''+rtrim(rid)
	+N''' then tagname else N'''' end)'
from #t group by qid,rid
exec('
select starttime,endtime'+@s+' 
from #t a
where not exists(
	select * from #t
	where qid=a.qid and rid=a.rid 
		and starttime=a.starttime
		and endtime=a.endtime
		and id<a.id)
group by starttime,endtime')
go

--調(diào)用
exec p_qry '2005-1-17'
exec p_qry '2005-1-18'
go

--刪除測(cè)試
drop table tb
drop proc p_qry

/*--測(cè)試結(jié)果

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   未訂         未訂
09:00     10:00   未訂         未訂
10:00     11:00   未訂         未訂

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   裝修         未訂
09:00     10:00   未訂         裝修
10:00     11:00   裝修         未訂
--*/