三个表(客房表,基础价格表,浮动价格表),1-2,1-3分别组成视图1和视图2.
两个价格表结构相同,字段如下: room_id, startdate,enddate, price,weekenddifinition,weekendprice.(两个价格表之间日期段可以重复,同一价格表内日期段不能重复。浮动价格表内数据优先。周末定义为char,)现有如下记录:基础表
1,'2011-07-09','2011-07-31', 188, 1|1|0|0|0|0|0, 198 表示7月9号到7月31号,价格188,逢周一周二,价格198。
1,'2011-08-01','2011-08-31',199, 0|0|0|0|0|1|1,209 表示8月份价格199,每逢周五周六,价格209浮动表
1,'2011-08-02','2011-08-09', 228, 0|0|0|0|0|1|1, 258要查询 7月25号-9月30号每天的价格。列出:日期,周几,价格。
2011-07-25 ,1,198.
………………
2011-08-05, 5, 258
………………
另一个帖子曾提到,原来的存储过程用的是循环日期和表变量的办法。
两位热心朋友说日期输出最好用 select DATEADD(DAY,number,@start) as dt,DATEPART (weekday,DATEADD(DAY,number,@start)) as xingqi
from master..spt_values
where type='P' and dateadd(d,number,@start)<@end
两个价格表结构相同,字段如下: room_id, startdate,enddate, price,weekenddifinition,weekendprice.(两个价格表之间日期段可以重复,同一价格表内日期段不能重复。浮动价格表内数据优先。周末定义为char,)现有如下记录:基础表
1,'2011-07-09','2011-07-31', 188, 1|1|0|0|0|0|0, 198 表示7月9号到7月31号,价格188,逢周一周二,价格198。
1,'2011-08-01','2011-08-31',199, 0|0|0|0|0|1|1,209 表示8月份价格199,每逢周五周六,价格209浮动表
1,'2011-08-02','2011-08-09', 228, 0|0|0|0|0|1|1, 258要查询 7月25号-9月30号每天的价格。列出:日期,周几,价格。
2011-07-25 ,1,198.
………………
2011-08-05, 5, 258
………………
另一个帖子曾提到,原来的存储过程用的是循环日期和表变量的办法。
两位热心朋友说日期输出最好用 select DATEADD(DAY,number,@start) as dt,DATEPART (weekday,DATEADD(DAY,number,@start)) as xingqi
from master..spt_values
where type='P' and dateadd(d,number,@start)<@end
gocreate table tb(roomno varchar(10),startdate datetime,enddate datetime,price int,weekenddifinition varchar(50),weekendprice int)
create table tb1(roomno varchar(10),startdate datetime,enddate datetime,price int,weekenddifinition varchar(50),weekendprice int)
insert into tb select '501','2011-07-15','2011-07-26',180,'1|1|0|0|0|0|0',198
insert into tb select '501','2011-07-31','2011-08-05',180,'0|0|1|1|0|0|0',209
insert into tb1 select '501','2011-07-25','2011-08-10',205,'0|0|0|0|0|1|1',300declare @start datetime,@end datetime,@roomno varchar(10)
set @start='2011-07-16'
set @end='2011-08-10';with cte as
(
select DATEADD(DAY,number,@start) as dt,DATEPART(DW,DATEADD(DAY,number,@start)) as wd
from master..spt_values
where type='P' and dateadd(d,number,@start)<=@end
)
,cte1 as
(
select A.dt,A.wd,case SUBSTRING(B.weekenddifinition,A.wd*2-1,1)when '0' then B.price else B.weekendprice end as price
from cte as A left outer join tb as B
on A.dt between B.startdate and B.enddate
)
select A.dt,ISNULL(A.price,case SUBSTRING(B.weekenddifinition,A.wd*2-1,1)when '0' then B.price else B.weekendprice end) as price,A.wd
from cte1 as A left outer join tb1 as B
on A.dt between B.startdate and B.enddatedt price wd
----------------------- ----------- -----------
2011-07-16 00:00:00.000 180 7
2011-07-17 00:00:00.000 198 1
2011-07-18 00:00:00.000 198 2
2011-07-19 00:00:00.000 180 3
2011-07-20 00:00:00.000 180 4
2011-07-21 00:00:00.000 180 5
2011-07-22 00:00:00.000 180 6
2011-07-23 00:00:00.000 180 7
2011-07-24 00:00:00.000 198 1
2011-07-25 00:00:00.000 198 2
2011-07-26 00:00:00.000 180 3
2011-07-27 00:00:00.000 205 4
2011-07-28 00:00:00.000 205 5
2011-07-29 00:00:00.000 300 6
2011-07-30 00:00:00.000 300 7
2011-07-31 00:00:00.000 180 1
2011-08-01 00:00:00.000 180 2
2011-08-02 00:00:00.000 209 3
2011-08-03 00:00:00.000 209 4
2011-08-04 00:00:00.000 180 5
2011-08-05 00:00:00.000 180 6
2011-08-06 00:00:00.000 300 7
2011-08-07 00:00:00.000 205 1
2011-08-08 00:00:00.000 205 2
2011-08-09 00:00:00.000 205 3
2011-08-10 00:00:00.000 205 4(26 行受影响)