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

waiking33 2005-01-18 02:33:03
有表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 装修 装修
这样的存储过程要怎么写呢?请高手指教




...全文
169 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
子陌红尘 2005-01-19
表中startdate 和 enddate 是什么类型?

把建表的SQL 语句或者是详细的表结构列出来
回复
waiking33 2005-01-19
还是不行呀!
回复
waiking33 2005-01-19
邹建的方法也可以,谢谢!(不好意思的是,因为帖子已经结掉没有分给了,下次再请教时补上)
回复
zjcxc 2005-01-19

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

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
回复
子陌红尘 2005-01-19
--生成测试数据:
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'

回复
waiking33 2005-01-19
startdate和enddate是smalldatetime类型,starttime和endtime也是smalldatetime类型,startweekday 和 endweekday是int类型
回复
KingOfFalcon 2005-01-19
占个位子
回复
passionke 2005-01-19
先up着,回头看
回复
wxmxiaoming 2005-01-18
这个要好看看!!!
先顶着吧。。。。。
回复
子陌红尘 2005-01-18
楼上的存储过程可能不完全满足楼主的需求,如需判断输入日期是否在某个区间之内等等条件,但是基本上的功能可以满足。
回复
子陌红尘 2005-01-18
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'
回复
iswear428 2005-01-18
搬个板凳,研究研究
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-01-18 02:33
社区公告
暂无公告