min value for a date

Hi,
I have the following query which returns
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id = A.rate_id
where
r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <= @.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >= @.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by
r.Rate_id, A.date
order by
A.date, minRate
rate_id minrate date
10026823 100 2008-02-08 00:00:00.000
10026824 150 2008-02-08 00:00:00.000
10026824 150 2008-02-09 00:00:00.000
10026823 100 2008-02-10 00:00:00.000
10026823 100 2008-02-11 00:00:00.000
10026823 100 2008-02-13 00:00:00.000
10026823 100 2008-02-14 00:00:00.000
However I want to exclude row 2 as it is the same day but a higher
rate. I am working on an extremley old legacy system which allows lots
of different rates for the same day to entered which does not help!.
The structure cannot be changed so I am stuck with what I have.
How can I rewrite my query without creating a temp table?Rippo
Can yopu post DDL+ sample data + an expected result?
What is the vesrion of SQL Server?
"Rippo" <info@.rippo.co.uk> wrote in message
news:b887b44a-f7cb-42f5-a103-5877c9237403@.m62g2000hsb.googlegroups.com...
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?|||Hi
it seems that rate_id is not needed in the group by
select min(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
order by A.date, minRate
Although the minimum rate may not correspond to the rate_id returned you
could try rejoining it to the original tables matching rate and date
(untested)
SELECT c.rate_id, b.MinRate, b.date
JOIN hotel_acc_rate_avail c
JOIN hotel_acc_rates S ON s.rate_id = c.rate_id
JOIN (
select min(r.rate) MinRate, A.date
from hotel_acc_rates R
join hotel_acc_rate_avail A on R.rate_id = A.rate_id
where r.hotel_id = 1147 -- Legacy falcon
and R.room_type_id = 72 -- Double room
and R.board_type = 6 -- Bed and breakfast
and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=@.year
and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=@.year
and r.min_nights = 1 -- Standard type
and a.date >= getDate() -- we dont go back in time
group by A.date
) b ON b.date = c.date AND b.minrate = s.rate
order by b.date, b.minRate
This would not eliminate two rate_ids with the same minimum rate, this would
require grouping again.
John
"Rippo" wrote:
> Hi,
> I have the following query which returns
> select
> r.Rate_id, min(r.rate) MinRate, A.date
> from
> hotel_acc_rates R
> inner join
> hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> r.hotel_id = 1147 -- Legacy falcon
> and R.room_type_id = 72 -- Double room
> and R.board_type = 6 -- Bed and breakfast
> and month(R.valid_from_date) <= @.month and year(R.valid_from_date) <=> @.year
> and month(R.valid_to_date) >= @.month and year(R.valid_to_date) >=> @.year
> and r.min_nights = 1 -- Standard type
> and a.date >= getDate() -- we dont go back in time
> group by
> r.Rate_id, A.date
> order by
> A.date, minRate
> rate_id minrate date
> 10026823 100 2008-02-08 00:00:00.000
> 10026824 150 2008-02-08 00:00:00.000
> 10026824 150 2008-02-09 00:00:00.000
> 10026823 100 2008-02-10 00:00:00.000
> 10026823 100 2008-02-11 00:00:00.000
> 10026823 100 2008-02-13 00:00:00.000
> 10026823 100 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
>|||On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
> Hi,
> I have the following query which returns
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.valid_=from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date) >=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> order by
> =A0 =A0 =A0 =A0 A.date, minRate
> rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> However I want to exclude row 2 as it is the same day but a higher
> rate. I am working on an extremley old legacy system which allows lots
> of different rates for the same day to entered which does not help!.
> The structure cannot be changed so I am stuck with what I have.
> How can I rewrite my query without creating a temp table?
Hi,
I have re-written your query a bit. You might try this although
untested.
select
r.Rate_id, min(r.rate) MinRate, A.date
from
hotel_acc_rates R
inner join
hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and R.room_type_id =3D 72 -- Double room
and R.board_type =3D 6 -- Bed and breakfast
and month(R.valid_from_date) <=3D @.month and
year(R.valid_from_date) <=3D
@.year
and month(R.valid_to_date) >=3D @.month and year(R.valid_to_date)
>=3D
@.year
and r.min_nights =3D 1 -- Standard type
and a.date >=3D getDate() -- we dont go back in
time
group by
r.Rate_id, A.date
having min(r.rate) =3D
(select min(s.rate)
from
hotel_acc_rates S
inner join
hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
where
r.hotel_id =3D 1147 -- Legacy falcon
and S.room_type_id =3D 72 -- Double room
and S.board_type =3D 6 -- Bed and breakfast
and month(S.valid_from_date) <=3D @.month and
year(S.valid_from_date) <=3D
@.year
and month(S.valid_to_date) >=3D @.month and year(S.valid_to_date)
>=3D
@.year
and s.min_nights =3D 1 -- Standard type
and b.date >=3D getDate() -- we dont go back in
time
and R.Rate_id =3D S.Rate_id
and A.date =3D B.date
group by
s.Rate_id, B.date
)
order by
A.date, minRate|||On Feb 11, 2:41=A0pm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15=A0pm, Rippo <i...@.rippo.co.uk> wrote:
>
>
> > Hi,
> > I have the following query which returns
> > select
> > =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> > from
> > =A0 =A0 =A0 =A0 hotel_acc_rates R
> > inner join
> > =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> > where
> > =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> > =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room=
> > =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and= breakfast
> > =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and year(R.vali=d_from_date) <=3D
> > @.year
> > =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_=to_date) >=3D
> > @.year
> > =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standar=d type
> > =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- =we dont go back in time
> > group by
> > =A0 =A0 =A0 =A0 r.Rate_id, A.date
> > order by
> > =A0 =A0 =A0 =A0 A.date, minRate
> > rate_id =A0 =A0 =A0 =A0 =A0 =A0minrate date
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-08 00:00:00.000
> > 10026824 =A0 =A0 =A0 =A0150 =A0 =A0 2008-02-09 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-10 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-11 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-13 00:00:00.000
> > 10026823 =A0 =A0 =A0 =A0100 =A0 =A0 2008-02-14 00:00:00.000
> > However I want to exclude row 2 as it is the same day but a higher
> > rate. I am working on an extremley old legacy system which allows lots
> > of different rates for the same day to entered which does not help!.
> > The structure cannot be changed so I am stuck with what I have.
> > How can I rewrite my query without creating a temp table?
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> =A0 =A0 =A0 =A0 r.Rate_id, min(r.rate) MinRate, A.date
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates R
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail A on R.rate_id =3D A.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and R.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and R.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(R.valid_from_date) <=3D @.month and
> year(R.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(R.valid_to_date) >=3D @.month and year(R.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and r.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and a.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> group by
> =A0 =A0 =A0 =A0 r.Rate_id, A.date
> having min(r.rate) =3D
> (select min(s.rate)
> from
> =A0 =A0 =A0 =A0 hotel_acc_rates S
> inner join
> =A0 =A0 =A0 =A0 hotel_acc_rate_avail B on S.rate_id =3D B.rate_id
> where
> =A0 =A0 =A0 =A0 r.hotel_id =3D 1147 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 -- Legacy falcon
> =A0 =A0 =A0 =A0 and S.room_type_id =3D 72 =A0 =A0 =A0 =A0 -- Double room
> =A0 =A0 =A0 =A0 and S.board_type =3D 6 =A0 =A0 =A0 =A0 =A0 =A0-- Bed and b=reakfast
> =A0 =A0 =A0 =A0 and month(S.valid_from_date) <=3D @.month and
> year(S.valid_from_date) <=3D
> @.year
> =A0 =A0 =A0 =A0 and month(S.valid_to_date) >=3D @.month and year(S.valid_to=_date)>=3D
> @.year
> =A0 =A0 =A0 =A0 and s.min_nights =3D 1 =A0 =A0 =A0 =A0 =A0 =A0-- Standard =type
> =A0 =A0 =A0 =A0 and b.date >=3D getDate() =A0 =A0 =A0 =A0 =A0 =A0 =A0-- we= dont go back in
> time
> =A0 =A0 =A0 =A0 and R.Rate_id =3D S.Rate_id
> =A0 =A0 =A0 =A0 and A.date =3D B.date
> group by
> =A0 =A0 =A0 =A0 s.Rate_id, B.date
> )
> order by
> =A0 =A0 =A0 =A0 A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
=2E...
and A.date =3D B.date
group by
B.date
)
HTH