Computed field with a maximum

Hi all,
I have a table with some fields, and I'm builiding a query upon it
which should have a computed field based upon the values in each row.
For example:
CREATE TABLE mytable(
-- ...
price decimal NOT NULL,
number decimal NOT NULL
);
The computed field should be the product of price and number fields,
multiplied by a coefficient, but should not be greater than a maximum
value. In MySQL, there is a function named IF(), which takes a
condition as the first parameter, and returns the second parameter if
the condition is true, or the third parameter if not. For example, I'd
write something like this in MySQL:
SELECT ..., price, number,
IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
How can I express something like this in SQL Server? I've browsed
through the functions supported by SQL Server, but I have not seen
anything which looks similar to the IF() function in MySQL.
Thanks in advance,The ANSI compliant functionality (which is implemented in SQL Server) is the
CASE expression.
Something like:
SELECT ..., price, number,
CASE WHEN price*number*0.004 > 10000 THEN 10000 ELSE price*number*0.004 END
FROM mytable;
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||SELECT answer = case when price * number * 0.004 > 10000 then 10000 else
price * number * 0.004 end
from mytable
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||A bit off topic, yet it might prove to be helpful...
Keep in mind that when declaring the decimal datatype the default scale (the
number of decimal digits) is 0.
ML

Complicated Cross-Tab Query

I have the following table and data:

tblDepartments: (each department can only have a maximum of 3 sections attached to it)

Columns: DepartmentName , SectionName

Row1: dep1, sec1.0
Row2: dep1, sec1.1
Row3: dep1, sec1.2

Row 4: dep2, sec2.0

Row 5: dep3, sec3.0
Row 6: dep3, sec3.1

I need to derive the following table from tblDepartments :

Columns: DepartmentName, SectionName1, SectionName2,
SectionName3

Row1: dep1, sec1.0, sec1.1, sec1.2
Row2: dep2, sec2.0, '', ''
Row3: dep3, sec3.0, sec3.1, ''

Any ideas?

SELECT DepartmentName,[1],[2],[3]

FROM(

SELECT DepartmentName,SectionName,(SELECT rFROM(SELECT SectionName,row_number()OVER(ORDERBY SectionName)AS rFROM tblDepartments d2WHERE d2.DepartmentName=d.DepartmentName) t1WHERE t1.SectionName=d.SectionName)As col

FROM tblDepartments d

) t2

PIVOT

(

MAX(SectionName)

FOR colIN([1],[2],[3]))AS pvt

ORDERBY DepartmentName

|||

Motley wrote:

SELECT DepartmentName,[1],[2],[3]

FROM(

SELECT DepartmentName,SectionName,(SELECT rFROM(SELECT SectionName,row_number()OVER(ORDERBY SectionName)AS rFROM tblDepartments d2WHERE d2.DepartmentName=d.DepartmentName) t1WHERE t1.SectionName=d.SectionName)As col

FROM tblDepartments d

) t2

PIVOT

(

MAX(SectionName)

FOR colIN([1],[2],[3]))AS pvt

ORDERBY DepartmentName

It looks like PIVOT is only for SQl Server 2005.

We haven't upgraded to that yet.|||Ah. So is row_number. Still doable, just much more difficult.

Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has same field name calcuated_price has max value is 3000.

Nishith

SELECTMAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECTCASE

WHENMAX(P.iPlanid)>MAX(AP.iPlanid)THENMAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

ENDAS maxvalue

FROM

PLANS P

INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen

Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has

same field name calcuated_price has max value is 3000.

Nishith

SELECT MAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECT CASE

WHEN MAX(P.iPlanid) > MAX(AP.iPlanid) THEN MAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

END AS maxvalue

FROM

PLANS P

INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen