Prev: Best way to format errors with raiserror?
Next: missing Index DMVs
From: meangene on 25 Mar 2010 14:58
In view design I wish a row to calculate the UnitDiscountAmt by multiplying
the UnitPrice * DiscountRate if DiscountRate >0, Else just show the
UnitPrice. Now if I were doing this in Access I would IIf([DiscountRate]=0,
[UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the view?
From: Tibor Karaszi on 25 Mar 2010 15:12
CASE (which is more generic than IIF and also ANSI SQL). Something like:

SELECT
col1
,CASE
WHEN DiscountRate > 0 THEN UnitPRice*DiscountRate
ELSE Unitprice
AS colname
END
,col3
FROM ...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"meangene" <meangene(a)discussions.microsoft.com> wrote in message
news:66249C6B-5B08-42CB-B851-DC6B269AD03C(a)microsoft.com...
> In view design I wish a row to calculate the UnitDiscountAmt by
> multiplying
> the UnitPrice * DiscountRate if DiscountRate >0, Else just show the
> UnitPrice. Now if I were doing this in Access I would
> IIf([DiscountRate]=0,
> [UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the
> view?

From: --CELKO-- on 25 Mar 2010 15:37
>> In view design I wish a row to calculate the UnitDiscountAmt by multiplying the UnitPrice * DiscountRate if DiscountRate >0, Else just show the UnitPrice. Now if I were doing this in Access I would IIf([DiscountRate]=0,[UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the view? <<

Have you considered making the default value of the discount rate 1.00
and always doing the multiplication? Or use ((1.00 - discount_rate) *
unit_price) ?

From: meangene on 26 Mar 2010 10:41
This did the trick - thanks!

"Tibor Karaszi" wrote:

> CASE (which is more generic than IIF and also ANSI SQL). Something like:
>
> SELECT
> col1
> ,CASE
> WHEN DiscountRate > 0 THEN UnitPRice*DiscountRate
> ELSE Unitprice
> AS colname
> END
> ,col3
> FROM ...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "meangene" <meangene(a)discussions.microsoft.com> wrote in message
> news:66249C6B-5B08-42CB-B851-DC6B269AD03C(a)microsoft.com...
> > In view design I wish a row to calculate the UnitDiscountAmt by
> > multiplying
> > the UnitPrice * DiscountRate if DiscountRate >0, Else just show the
> > UnitPrice. Now if I were doing this in Access I would
> > IIf([DiscountRate]=0,
> > [UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the
> > view?
>
> .
>
 | 
Pages: 1
Prev: Best way to format errors with raiserror?
Next: missing Index DMVs