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
100268231002008-02-08 00:00:00.000
100268241502008-02-08 00:00:00.000
100268241502008-02-09 00:00:00.000
100268231002008-02-10 00:00:00.000
100268231002008-02-11 00:00:00.000
100268231002008-02-13 00:00:00.000
100268231002008-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
selectmin(r.Rate_id) as Rate_id, min(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.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 byA.date
order byA.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 (
selectmin(r.rate) MinRate, A.date
fromhotel_acc_rates R
joinhotel_acc_rate_avail A on R.rate_id = A.rate_id
wherer.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 byA.date
) b ON b.date = c.date AND b.minrate = s.rate
order byb.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
> 100268231002008-02-08 00:00:00.000
> 100268241502008-02-08 00:00:00.000
> 100268241502008-02-09 00:00:00.000
> 100268231002008-02-10 00:00:00.000
> 100268231002008-02-11 00:00:00.000
> 100268231002008-02-13 00:00:00.000
> 100268231002008-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 11, 2:41Xpm, SB <othell...@.yahoo.com> wrote:
> On Feb 7, 2:15Xpm, Rippo <i...@.rippo.co.uk> wrote:
>
>
>
>
>
> Hi,
> I have re-written your query a bit. You might try this although
> untested.
> select
> X X X X r.Rate_id, min(r.rate) MinRate, A.date
> from
> X X X X hotel_acc_rates R
> inner join
> X X X X hotel_acc_rate_avail A on R.rate_id = A.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and R.room_type_id = 72 X X X X -- Double room
> X X X X and R.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(R.valid_from_date) <= @.month and
> year(R.valid_from_date) <=
> @.year
> X X X X and month(R.valid_to_date) >= @.month and year(R.valid_to_date)>=
> @.year
> X X X X and r.min_nights = 1 X X X X X X-- Standard type
> X X X X and a.date >= getDate() X X X X X X X-- wedont go back in
> time
> group by
> X X X X r.Rate_id, A.date
> having min(r.rate) =
> (select min(s.rate)
> from
> X X X X hotel_acc_rates S
> inner join
> X X X X hotel_acc_rate_avail B on S.rate_id = B.rate_id
> where
> X X X X r.hotel_id = 1147 X X X X X X X X X X X -- Legacy falcon
> X X X X and S.room_type_id = 72 X X X X -- Double room
> X X X X and S.board_type = 6 X X X X X X-- Bed and breakfast
> X X X X and month(S.valid_from_date) <= @.month and
> year(S.valid_from_date) <=
> @.year
> X X X X and month(S.valid_to_date) >= @.month and year(S.valid_to_date)>=
> @.year
> X X X X and s.min_nights = 1 X X X X X X-- Standard type
> X X X X and b.date >= getDate() X X X X X X X-- wedont go back in
> time
> X X X X and R.Rate_id = S.Rate_id
> X X X X and A.date = B.date
> group by
> X X X X s.Rate_id, B.date
> )
> order by
> X X X X A.date, minRate- Hide quoted text -
> - Show quoted text -
Change the last part to:
...
and A.date = B.date
group by
B.date
)
HTH