minvalue and maxvalue in sequence

Hello everyone:

I try to create a sequence with following requirements:

create a sequence that will generate integers starting with the value 9. Each value should be three less than the previous value generated. The lowest possible balue the sequence should be allowed to generate is -1, and it should not be allowed to cycle.

I think values generated by this sequence are 9, 6, 3, 0

my sql statement goes like this:

SQL> create sequence MY_FIRST_SEQUENCE
2 increment by -3
3 start with 9
4 minvalue -1
5 nocycle;
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE

I don't know what's the maxvalue in this squence. Should it be 9 if that's the case. why do I get a error message?

Plase give me some suggestio on how can I make this sequence work. Thanks!

sjgrad03
12-03Even without knowing anything about sequences, if this is a DESCENDING sequence and it STARTS WITH 9, then it is logical that its MAXIMUM VALUE is 9.

Your statement will be executed properly if you add MAXVALUE n, where n >= START_WITH_value. If it is not clear enough, here's an example:

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 9
MINVALUE -1
INCREMENT BY -3;

or

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 1000
MINVALUE -1
INCREMENT BY -3;

NOCYCLE is the default and it is not necessary to specify it.

Read more about creating Oracle sequences here (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm).

minimum value

i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA

minimum value

i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql

minimum value

i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA

Minimum scale of y-axis ignored when using negative value

I want to display a chart with a minimum y-axis of -10%. When use the value -0.10 the result is a minimum of -100%. Is this a bug?

-Jeroen

Hi,

I have the same problem since 2 days ... Nobody can help us ?

It's very important !

David

|||Hi David,

We've bought Dundas Chart to work around this problem because we didn't want to waint longer for a solution by Microsoft. (FYI: the charting component in SSRS is provided by Dundas)

Succes!

Jeroen

Minimum scale of y-axis ignored when using negative value

I want to display a chart with a minimum y-axis of -10%. When use the value -0.10 the result is a minimum of -100%. Is this a bug?

-Jeroen

Hi,

I have the same problem since 2 days ... Nobody can help us ?

It's very important !

David

|||Hi David,

We've bought Dundas Chart to work around this problem because we didn't want to waint longer for a solution by Microsoft. (FYI: the charting component in SSRS is provided by Dundas)

Succes!

Jeroen

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

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

Min value

CurrentStage resides in the BUSLOCATION table and UserStage resides in the USERLOCATION table.

I need to find out the MIN difference between these two columns

i.e UserStage-CurrentStage=SHOULD BE THE MINIMUM VALUE.

Userstage is just one value but then it shud be subtracted from all the CurrentStage values in the table and the CurrentStage value which gives me the least difference should be extracted out.

Hope i didn't confuse u all too much :P anyone any ideas?so something like

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage = min(t1.UserStage - t2.CurrentStage )

yeah???|||you might need to go to...

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage =
(select min(t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is )

not sure though....|||Do u think my code would work?

SELECT CurrentStage, PlateNbr from tblBusLocation a, tblUserLocation b
Where a.CurrentStage < b.UserStageNbr AND a.CurrentStage= (SELECT MIN(b.UserStageNbr-a.CurrentStage))|||Sorry here is an updated code. Is there a problem with the MIN function?

SELECT t1.CurrentStage, t1.PlateNbr

From tblBusLocation t1, tblUserLocation t2

Where t1.CurrentStage < t2.UserStageNbr AND

t1.CurrentStage =

(SELECT t1.CurrentStage from tblBusLocation t1, tblUserLocation t2

where MIN (t2.UserStageNbr - t1.CurrentStage)
)|||SELECT t1.CurrentStage, t1.PlateNbr
, MIN (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1
, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
group
by t1.CurrentStage, t1.PlateNbr

rudy|||this might be way off but...

select top 1 (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
Order by (t2.UserStageNbr - t1.CurrentStage)

???

Min except for zero

We have a complex query with many aggregate functions.
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:

>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:

> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim

MIN and MAX strange results

I want to get the MIN and MAX value of a table column from a specified period of time. I execute a query and it return the result. The problem is that the values returned by MIN and MAX are not always correct!!

This is the result table

Date Statement From To

1 2007-01-03 00:00:00 Invoice 1 2 Correct
2 2007-01-04 00:00:00 Receipt 1 1 Correct
3 2007-01-04 00:00:00 Invoice 10 9 Wrong
4 2007-01-05 00:00:00 Receipt 2 5 Correct
5 2007-01-05 00:00:00 Invoice 100 99 Wrong
6 2007-01-08 00:00:00 Invoice 124 175 Correct
7 2007-01-09 00:00:00 Invoice 176 224 Correct
8 2007-01-10 00:00:00 Invoice 225 265 Correct

From =From Statement Number

To= To statement Number

The odd behavior happens when the number of digits changes. If the range of the column is 1 digit ie from 0 to 9 the values reported are ok. If the digits change then there is a problem as in line 3 and 5.

Any ideas why this odd behavior happens?

rectis:

I think you need to provide (1) the SQL Statement that is not working correctly and the definition of the table (or at least the relevant columns). My knee-jerk guess would be that you are coming to grief because your "From" and "To" fields are defined as varchar instead of numeric (or integer).

If in fact your "from" and "to" fields are defined as varchar you first need to make a determination to the usage of these fields -- that is see if the definition needs to be modified such that columns are reformatted into numeric (or integer) columns. You may need to compute your max as MIN(CONVERT(INTEGER, FROM)) and MAX(CONVERT(INTEGER,TO))

|||You are right. Thank you very much. I think my brain was stopped.The field was defined as varchar. Now the values are ok!

MIMEType warning

Getting warning in reporting services report:
The value of the MIMEType property for the image ?image1? is ', which
is not a valid MIMEType.
my image is a .GIF and source is set to External. It is my understanding
that the MIMEType is ignored when source is External. I have also tried
setting the MIMETYPE to ="image/gif".
Any ideas'
ThanksWhen accessing an external image over a network protocol (e.g. http://...),
the target webserver should respond with the image data and the content
mimetype. If the target webserver provides an incorrect mimetype, it would
result in this type of warning message.
For external images from a network share or local paths we will try to
determine the mimetype based on the file extension (by a registry lookup).
In the very unlikely case that this registry lookup fails, the mimetype
would be empty.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pb" <pbrechlin@.hotmail.com> wrote in message
news:O33ItSriEHA.632@.TK2MSFTNGP12.phx.gbl...
> Getting warning in reporting services report:
> The value of the MIMEType property for the image ?image1? is ', which
> is not a valid MIMEType.
> my image is a .GIF and source is set to External. It is my understanding
> that the MIMEType is ignored when source is External. I have also tried
> setting the MIMETYPE to ="image/gif".
> Any ideas'
> Thanks|||What do I need to look at in order to correct this if the webserver is
not providing the correct info?
I just realized that this problem only happens when I run the report
within the development environment. When I deploy the report and run it
the correct image appears in the report.
Robert Bruckner [MSFT] wrote:
> When accessing an external image over a network protocol (e.g. http://...),
> the target webserver should respond with the image data and the content
> mimetype. If the target webserver provides an incorrect mimetype, it would
> result in this type of warning message.
> For external images from a network share or local paths we will try to
> determine the mimetype based on the file extension (by a registry lookup).
> In the very unlikely case that this registry lookup fails, the mimetype
> would be empty.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pb" <pbrechlin@.hotmail.com> wrote in message
> news:O33ItSriEHA.632@.TK2MSFTNGP12.phx.gbl...
>>Getting warning in reporting services report:
>>The value of the MIMEType property for the image ?image1? is ', which
>>is not a valid MIMEType.
>>my image is a .GIF and source is set to External. It is my understanding
>>that the MIMEType is ignored when source is External. I have also tried
>>setting the MIMETYPE to ="image/gif".
>>Any ideas'
>>Thanks
>
>|||Is the development environment on the same machine as the report server? If
not, probably there is a proxy or firewall involved when running it in the
development environment and therefore the mime type does not come through
correctly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23Xh8GUEjEHA.3972@.tk2msftngp13.phx.gbl...
> What do I need to look at in order to correct this if the webserver is
> not providing the correct info?
> I just realized that this problem only happens when I run the report
> within the development environment. When I deploy the report and run it
> the correct image appears in the report.
> Robert Bruckner [MSFT] wrote:
> > When accessing an external image over a network protocol (e.g.
http://...),
> > the target webserver should respond with the image data and the content
> > mimetype. If the target webserver provides an incorrect mimetype, it
would
> > result in this type of warning message.
> > For external images from a network share or local paths we will try to
> > determine the mimetype based on the file extension (by a registry
lookup).
> > In the very unlikely case that this registry lookup fails, the mimetype
> > would be empty.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "pb" <pbrechlin@.hotmail.com> wrote in message
> > news:O33ItSriEHA.632@.TK2MSFTNGP12.phx.gbl...
> >
> >>Getting warning in reporting services report:
> >>
> >>The value of the MIMEType property for the image ?image1? is ', which
> >>is not a valid MIMEType.
> >>
> >>my image is a .GIF and source is set to External. It is my understanding
> >>that the MIMEType is ignored when source is External. I have also tried
> >>setting the MIMETYPE to ="image/gif".
> >>
> >>Any ideas'
> >>Thanks
> >
> >
> >|||I began to get this error right after I installed "Office 2003 Pro".
Do you use MS Office 2003?
"Robert Bruckner [MSFT]" wrote:
> Is the development environment on the same machine as the report server? If
> not, probably there is a proxy or firewall involved when running it in the
> development environment and therefore the mime type does not come through
> correctly.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pb" <pbrechlin@.hotmail.com> wrote in message
> news:%23Xh8GUEjEHA.3972@.tk2msftngp13.phx.gbl...
> > What do I need to look at in order to correct this if the webserver is
> > not providing the correct info?
> >
> > I just realized that this problem only happens when I run the report
> > within the development environment. When I deploy the report and run it
> > the correct image appears in the report.
> >
> > Robert Bruckner [MSFT] wrote:
> > > When accessing an external image over a network protocol (e.g.
> http://...),
> > > the target webserver should respond with the image data and the content
> > > mimetype. If the target webserver provides an incorrect mimetype, it
> would
> > > result in this type of warning message.
> > > For external images from a network share or local paths we will try to
> > > determine the mimetype based on the file extension (by a registry
> lookup).
> > > In the very unlikely case that this registry lookup fails, the mimetype
> > > would be empty.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "pb" <pbrechlin@.hotmail.com> wrote in message
> > > news:O33ItSriEHA.632@.TK2MSFTNGP12.phx.gbl...
> > >
> > >>Getting warning in reporting services report:
> > >>
> > >>The value of the MIMEType property for the image â'image1â' is â'â', which
> > >>is not a valid MIMEType.
> > >>
> > >>my image is a .GIF and source is set to External. It is my understanding
> > >>that the MIMEType is ignored when source is External. I have also tried
> > >>setting the MIMETYPE to ="image/gif".
> > >>
> > >>Any ideas'
> > >>Thanks
> > >
> > >
> > >
>
>