统计--交叉表+日期+优先.sql

SQL code

--交叉表,根据优先级取数据,日期处理 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',2union 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',2union 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',2go /*--楼主这个问题要考虑几个方面 1. 取星期时,set datefirst 的影响 2. 优先级问题 3. qid,rid 应该是未知的(动态变化的) --*/ --实现的存储过程如下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 --调用exec p_qry '2005-1-17' exec p_qry '2005-1-18' go --删除测试drop table tb drop proc p_qry /*--测试结果 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 装修 未订 --*/