有挑战性的存储过程（欢迎各高手进来指教，邹建大哥进来看看，在线等）

[waiking33]

有表a(qid,tagname,rid,starttime,endtime,startweekday,endweekday，startdate，enddate)
各字段的含义 qid(楼层号）rid（房间编号），tagname(房间的状态），starttime（开始时间）endtime（结束时间），startweekday（开始星期），endweekday（结束星期），startdate（开始日期），enddate（结束日期），d（为优先级2比1高）
表内容如下：
qid rid tagname starttime endtime startweekday endweekday startdate enddate d
1 A1 未订 8：00 9：00 1 5 null null 1
1 A1 未订 9：00 10：00 1 5 null null 1
1 A1 未订 10：00 11：00 1 5 null null 1
1 A1 装修 8：00 9：00 null null 2005-1-18 2005-1-19 2
1 A1 装修 9：00 10：00 null null 2005-1-18 2005-1-19 2
1 A1 装修 10：00 11：00 null null 2005-1-18 2005-1-19 2
1 A2 未订 8：00 9：00 1 5 null null 1
1 A2 未订 9：00 10：00 1 5 null null 1
1 A2 未订 10：00 11：00 1 5 null null 1
1 A2 装修 8：00 9：00 null null 2005-1-18 2005-1-19 2
1 A2 装修 9：00 10：00 null null 2005-1-18 2005-1-19 2
1 A2 装修 10：00 11：00 null null 2005-1-18 2005-1-19 2
startweekday ，endweekday代表平常周一到周五时各房间在不同时段的状态。startdate，enddate代表特殊日期各房间各时段的状态。要形成如下的报表存储过程改如何写（传入的参数为日期如2005-1-17号，为星期一应该显示常规的周一到周五的状态，如果传入的为2005-1-18虽然为星期二，但我们已特殊日期的优先级高，显示特殊日期时的设置）现在我们传入2005-1-17要显示成如下报表：
A1 A2
08：00－09：00 未订 未订
09：00－10：00 未订 未订
11：00－12：00 未订 未订
传入2005-1-18时显示如下
08：00－09：00 装修 装修
09：00－10：00 装修 装修
11：00－12：00 装修 装修
这样的存储过程要怎么写呢？请高手指教

[iswear428]

搬个板凳，研究研究

[子陌红尘]

create procedure prd_test2(@t varchar(20))
as
begin
declare @t1 datetime
set @t1 = cast(@t as datetime)
select
(aa.starttime + '-' + aa.endtime) as times,
aa.tagname as A1,
bb.tagname as A2
from
(select * from a where rid = 'A1') aa,
(select * from a where rid = 'A2') bb
where
aa.qid = bb.qid and aa.starttime = bb.starttime and aa.endtime = bb.endtime and
aa.startweekday = bb.startweekday and aa.endweekday = bb.endweekday and aa.d = 1 and
not exists(select 1 from a where (@t1 between startdate and enddate) and d = 2)
union all
select
(cc.starttime + '-' + cc.endtime) as times,
cc.tagname as A1,
dd.tagname as A2
from
(select * from a where rid = 'A1') cc,
(select * from a where rid = 'A2') dd
where
cc.qid = dd.qid and cc.starttime = dd.starttime and cc.endtime = dd.endtime and cc.d = 2 and
(@t1 between cc.startdate and dd.enddate)
end

GO


exec prd_test2 '2005-1-17'
exec prd_test2 '2005-1-18'

[子陌红尘]

楼上的存储过程可能不完全满足楼主的需求，如需判断输入日期是否在某个区间之内等等条件，但是基本上的功能可以满足。

[wxmxiaoming]

这个要好看看！！！
先顶着吧。。。。。

[passionke]

先up着，回头看

[KingOfFalcon]

占个位子

[waiking33]

startdate和enddate是smalldatetime类型，starttime和endtime也是smalldatetime类型，startweekday 和 endweekday是int类型

[子陌红尘]

--生成测试数据：
create table tt(
qid int,
rid varchar(20),
tagname varchar(20),
starttime smalldatetime,
endtime smalldatetime,
startweekday int,
endweekday int,
startdate smalldatetime,
enddate smalldatetime,
d int)

go

insert into tt values(1,'A1','未订',cast('08:00' as smalldatetime),cast('09:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A1','未订',cast('09:00' as smalldatetime),cast('10:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A1','未订',cast('10:00' as smalldatetime),cast('11:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A2','未订',cast('08:00' as smalldatetime),cast('09:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A2','未订',cast('09:00' as smalldatetime),cast('10:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A2','未订',cast('10:00' as smalldatetime),cast('11:00' as smalldatetime), ,1 ,5 ,null ,null ,1)
insert into tt values(1,'A2','装修',cast('08:00' as smalldatetime),cast('09:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)
insert into tt values(1,'A2','装修',cast('09:00' as smalldatetime),cast('10:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)
insert into tt values(1,'A2','装修',cast('10:00' as smalldatetime),cast('11:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)
insert into tt values(1,'A1','装修',cast('08:00' as smalldatetime),cast('09:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)
insert into tt values(1,'A1','装修',cast('09:00' as smalldatetime),cast('10:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)
insert into tt values(1,'A1','装修',cast('10:00' as smalldatetime),cast('11:00' as smalldatetime), ,null ,null ,cast('2005-1-18' as smalldatetime) ,cast('2005-1-19' as smalldatetime) ,2)



--创建存储过程：
create procedure prd_test2(@t varchar(20))
as
begin
declare @t1 datetime
set @t1 = cast(@t as datetime)
select
(convert(varchar(5),aa.starttime,114) + '-' + convert(varchar(5),aa.endtime,114)) as times,
aa.tagname as A1,
bb.tagname as A2
from
(select * from tt where rid = 'A1') aa,
(select * from tt where rid = 'A2') bb
where
aa.qid = bb.qid and aa.starttime = bb.starttime and aa.endtime = bb.endtime and
aa.startweekday = bb.startweekday and aa.endweekday = bb.endweekday and aa.d = 1 and
not exists(select 1 from tt where (@t1 between startdate and enddate) and d = 2)
union all
select
(convert(varchar(5),cc.starttime,114) + '-' + convert(varchar(5),cc.endtime,114)) as times,
cc.tagname as A1,
dd.tagname as A2
from
(select * from tt where rid = 'A1') cc,
(select * from tt where rid = 'A2') dd
where
cc.qid = dd.qid and cc.starttime = dd.starttime and cc.endtime = dd.endtime and cc.d = 2 and
(@t1 between cc.startdate and dd.enddate)
end
GO



--执行存储过程
exec prd_test2 '2005-1-17'
exec prd_test2 '2005-1-18'

[zjcxc]

/*--楼主这个问题要考虑几个方面

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

[waiking33]

邹建的方法也可以，谢谢！（不好意思的是，因为帖子已经结掉没有分给了，下次再请教时补上）

[waiking33]

还是不行呀！

[子陌红尘]

表中startdate 和 enddate 是什么类型？

把建表的SQL 语句或者是详细的表结构列出来