SQL Server add computed column in VIEW with conditions

Question

Here is my situation:

In my table i two fields:

- Price (decimal (15,4)
- TaxId (int)

TaxId value is not stored anywhere but there are only two values (actualy 3).

- 1 (8.5%)
- 2 (20%)
- NULL (no tax)

Now i need calculated column in my view that would calculate new price with tax included.

Any idea?

I went with something like:

SELECT...
CASE TaxId
   WHEN 1 THEN Price *1.085 AS test
   WHEN 2 THEN Price *1.2 AS test
   WHEN NULL THEN Price  AS test END
FROM...

UPDATE:

I have managed to execute the query with success.

CASE dbo.Table.TaxId WHEN 1 THEN dbo.Table.Price*1.085 WHEN 2 THEN dbo.Table.Price*1.2 ELSE dbo.Table.Price END AS CalcualtedPrice

Now I only need to make CalculatedPrice as decimal (15,4). How can i set that?

You are causing trouble for yourself. Why don't you either 1) store the tax rate in a column and ditch the `TaxId` column or 2) store the tax rates in another table. — jwg, Jan 23 '14 at 14:07

fancyPants · Accepted Answer · 2012-06-06T10:55:41.667

7

Yes, google how to create a view in SQL Server and in the SELECT statement of your view multiply the Price column with the TaxId column. Since you have to consider the NULL value you should use COALESCE like this

SELECT
Price * (1 + COALESCE(TaxId/100), 0) AS newValue
...

assuming that you have just '8.5' in your column and not '8.5%'. Is it varchar or not?

P.S.: COALESCE() returns the first non-null value

UPDATE after question was edited:

You just have to slightly modify your syntax

SELECT 
CAST(
CASE 
   WHEN TaxId = 1 THEN Price *1.085
   WHEN TaxId = 2 THEN Price *1.2
   WHEN TaxId IS NULL THEN Price END AS decimal(15,4)
) AS CalculatedPrice
FROM...

edited Jun 06 '12 at 10:55

answered Jun 06 '12 at 10:29

fancyPants

50,732
33
89
96

i dont have actual tax values just some codes (integer). I know that 1 is 8.5, 2 is 20. And it could also be NULL that means no tax. I need one additional column that would calculate my price based on the TaxID. – no9 Jun 06 '12 at 10:32
Your edit "I went something like this..." seems fine. What's the problem? Oh, but delete the "AS test"s and write it once after "END", like `CASE WHEN ... END AS test` – fancyPants Jun 06 '12 at 10:36
works, now i cant figure out how to set the new column type to decimal(15,4) – no9 Jun 06 '12 at 10:50
@no9 See Andriy M's comment: CAST(CASE … END AS decimal(15,4)) AS CalculatedPrice – fancyPants Jun 06 '12 at 10:53

score 0 · Answer 2 · answered Oct 17 '18 at 09:00

If you are calculated field is jut number and it based on calculation it can directly be achieve through simple calculation as follows.

CREATE VIEW [dbo].[TestComputedViewColumn]
AS
SELECT   Tab1.Col1, Tab1.Col2, CONVERT(varchar, Tab1.Col1 + Tab2.Col2) AS 
[ComputedViewColumn]
FROM         Tab1 
GO

If the column which is being computed is string then it can be using sql CASE. below is an example of boolean column is being computed to either "YES" or "NO"

select CASE dbo.table1.Payment WHEN 1 THEN 'Yes' ELSE 'No' END AS 
PaymentText from dbo.table1

SQL Server add computed column in VIEW with conditions

2 Answers2