时间段查询

时间段查询

SQL code
请教一个问题，有如下数据表结构数据，按要求查询出相对应的数据
ID   HotelCode   RoomType    StratDate     EndDate       Price
1    00001        Twin       2009-01-01    [color=#FF0000]2009-03-01    [/color]200.00
2    00001        Twin       [color=#FF0000]2009-03-04    [/color]2009-04-03    300.00
3    00001        Twin       2009-04-04    2009-05-03    400.00
4    00001        Single     2009-01-04    2009-03-01    100.00
5    00001        Single     2009-03-02    2009-04-03    80.00
5    00001        Single     2009-03-04    2009-05-03    120.00
6    00002        Twin       2009-01-01    2009-03-01    200.00
7    00002        Twin       2009-03-02    2009-04-03    300.00
8    00002        Twin       2009-04-04    2009-05-03    400.00
9    00002        Single     2009-01-04    [color=#FF0000]2009-03-01    [/color]100.00
10   00002        Single     [color=#FF0000]2009-03-04[/color]    2009-04-03    100.00
11   00002        Single     2009-03-04    2009-05-03    100.00

要求查询  2009-03-01  --  2009-03-06时间段内的数据
查询的结果要求如下：
SQL code
ID   HotelCode    RoomType   Startdate     endDate       price
4    00001        Single     2009-01-04    2009-03-01    100.00
5    00001        Single     2009-03-02    2009-04-03    80.00
6    00002        Twin       2009-01-01    2009-03-01    200.00
7    00002        Twin       2009-03-02    2009-04-03    300.00

或者查询结果显示每天的价格也行。

SQL codecreate table test(ID int,HotelCode varchar(10),RoomType varchar(10),StartDate datetime,EndDate datetime,Price money)
insert into test select 1 ,'00001','Twin  ','2009-01-01','2009-03-01',200.00
insert into test select 2 ,'00001','Twin  ','2009-03-04','2009-04-03',300.00
insert into test select 3 ,'00001','Twin  ','2009-04-04','2009-05-03',400.00
insert into test select 4 ,'00001','Single','2009-01-04','2009-03-01',100.00
insert into test select 5 ,'00001','Single','2009-03-02','2009-04-03', 80.00
insert into test select 5 ,'00001','Single','2009-03-04','2009-05-03',120.00
insert into test select 6 ,'00002','Twin  ','2009-01-01','2009-03-01',200.00
insert into test select 7 ,'00002','Twin  ','2009-03-02','2009-04-03',300.00
insert into test select 8 ,'00002','Twin  ','2009-04-04','2009-05-03',400.00
insert into test select 9 ,'00002','Single','2009-01-04','2009-03-01',100.00
insert into test select 10,'00002','Single','2009-03-04','2009-04-03',100.00
insert into test select 11,'00002','Single','2009-03-04','2009-05-03',100.00
go

create function f_getInfo(@startDate datetime,@endDate datetime)
returns @t table(ID int,HotelCode varchar(10),RoomType varchar(10),StartDate datetime,EndDate datetime,Price money)
as
begin
    declare @t2 table(ID int,HotelCode varchar(10),RoomType varchar(10),StartDate datetime,EndDate datetime)
   
    insert into @t2
    select
        n.*
    from
        (select
             a.id,a.HotelCode,a.RoomType,a.StartDate,min(b.EndDate) as EndDate
         from
             (select t.* from test t where not exists(select 1 from test where HotelCode=t.HotelCode and RoomType=t.RoomType and datediff(dd,EndDate,t.StartDate)=1)) a,
             (select t.* from test t where not exists(select 1 from test where HotelCode=t.HotelCode and RoomType=t.RoomType and datediff(dd,t.EndDate,StartDate)=1)) b
         where
             a.HotelCode=b.HotelCode and a.RoomType=b.RoomType
             and
             a.StartDate<=b.EndDate
         group by
             a.id,a.HotelCode,a.RoomType,a.StartDate) n
    where
        @startDate between n.StartDate and n.EndDate
        and
        @endDate   between n.StartDate and n.EndDate
       
    insert into @t select * from test t where exists(select 1 from @t2 where id=t.id)
   
    while @@rowcount>0
    begin
        insert into @t
        select
            a.*
        from
            test a,@t b
        where
            a.HotelCode=b.HotelCode and a.RoomType=b.RoomType and b.EndDate+1=a.StartDate
            and
            a.StartDate between @startDate and @endDate
            and
            not exists(select 1 from @t where id=a.id)
    end
   
    return
end
go

select * from dbo.f_getInfo('2009-03-01' ,'2009-03-06') order by HotelCode,StartDate
/*
ID          HotelCode  RoomType   StartDate                                              EndDate                                                Price                
----------- ---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ---------------------
4           00001      Single     2009-01-04 00:00:00.000                                2009-03-01 00:00:00.000                                100.0000
5           00001      Single     2009-03-02 00:00:00.000                                2009-04-03 00:00:00.000                                80.0000
6           00002      Twin       2009-01-01 00:00:00.000                                2009-03-01 00:00:00.000                                200.0000
7           00002      Twin       2009-03-02 00:00:00.000                                2009-04-03 00:00:00.000                                300.0000
*/
go

drop function f_getInfo
drop table test
go