Calculation within a View

# Thread: Calculation within a View

1. Junior Member
Join Date
Dec 1969
Posts
22

## Calculation within a View

My question is regarding SQL.<BR><BR>In SQL Server 2000 I have created a View to read some data from a table and I need the view to make a calculation based on the result of a Bit field in the table.<BR><BR>Table:<BR>ItemId - int<BR>Price - money<BR>Tax Applies - bit<BR><BR>ie, If the field &#039;TaxApplies&#039; (bit) is equal to 1, I want to multiply the field &#039;Price&#039; by 1.1. - which would in effect add 10%.<BR><BR>Therefor, if there were two rows as follows:<BR>ItemId &#124 Price &#124 TaxApplies<BR>100 &#124 10 &#124 1<BR>101 &#124 10 &#124 0<BR><BR>The view should actually return:<BR>ItemId &#124 Price<BR>100 &#124 11<BR>101 &#124 10<BR><BR>Does anybody know how this can be done?<BR><BR>I know this can&#039;t work but it&#039;s kind of what needs to happen:<BR><BR>SELECT (IF ( TaxApplies = 1 ) ( Price * 1.1 ) ELSE Price ) AS Price<BR><BR>FROM View<BR><BR>Would love some help with this one!

2. Senior Member
Join Date
Dec 1969
Posts
10,852

## RE: Calculation within a View

An ugly way that works:<BR><BR>[code language="T-SQL"]<BR><BR>CREATE VIEW vwItems<BR>AS<BR> SELECT ItemID, Price FROM Items WHERE TaxApplies = 0<BR> UNION<BR> SELECT ItemID, (Price * 1.1) As Price FROM Items WHERE TaxApplies = 1<BR>[/code]

3. Junior Member
Join Date
Dec 1969
Posts
22

## RE: Calculation within a View

It&#039;s okay, I found it:<BR><BR>SELECT Price = (CASE TaxApplies WHEN 1 THEN (Price * 1.1) ELSE (Price) END)<BR>FROM Items<BR><BR>Works perfectly

4. Senior Member
Join Date
Dec 1969
Posts
10,852

## That works too.

One other way, just for kicks (&#039;cause I&#039;ve already found it):<BR><BR>[code language="T-SQL"]<BR>CREATE VIEW vwItems<BR>AS<BR> SELECT ItemID, (Price * (1 + (CAST(TaxApplies AS REAL) / 10))) AS Price FROM Items <BR>[/code]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•