Scalar function as part of a computed column
I am using SQL Server 2012. I want to create a table with a calculated column. This column is the sum of the columns in this table, but I need to multiply the amount by the VAT rate from the VAT table to get the gross value including tax. VAT values are entered into the VAT table every month and stored in the Stawka column as smallint (in English, this means "Rate"). The values in the Prąd table are also inserted every month, so I need to use the current VAT rate from the Stawka column in the VAT table.
So, if the tax rate (columnn Stawka in the VAT table) is currently 23%, that's simple math: (Y + Z) + 23% * (Y + Z) = (Y + Z) * (1 + 0 , 23) = (Y + Z) * 1.23, where Y + Z is the sum of the decimal columns up to the last in the prąd table.
Example of a row from the table Prąd. Below in the last column (computed column) it is just the sum of 0.2661 + 0.2103. As above, first I need to sum 0.2103 and 0.2661 and then multiply the sum by 1.23:
ID Data Rok Kwartał Miesiąc Dzień Jednostka Odczyt Zużycie Energia elektryczna czynna Opłata dystrybucyjna zmienna Stawka jednostkowa energii
1 2015-01-01 2015 1 1 1 kWh 300 20 0.2661 0.2103 0.4764
I cannot change the value to 1.23 because the VAT rate might change, so I decided to create a VAT table, but I don't know how to use those values in the Stawka column inside the calculated column.
I tried to follow some hints I found here and use a scalar function to get the current VAT and concatenation value, but I can't seem to use it as part of a whole calculation. Below is the code:
The table with the computed column I am trying to create (when creating the table, the column with the error "Stawka" appears):
CREATE TABLE dbo.Prąd
(
ID INT NOT NULL IDENTITY (1,1),
Data DATE NOT NULL,
Rok AS YEAR(Data) PERSISTED NOT NULL,
Kwartał AS DATEPART(q,Data) PERSISTED NOT NULL,
Miesiąc AS MONTH(Data) PERSISTED NOT NULL,
Dzień AS DAY(Data) PERSISTED NOT NULL,
Jednostka VARCHAR (5) NULL,
Odczyt SMALLINT NOT NULL,
Zużycie SMALLINT NOT NULL,
[Energia elektryczna czynna] DECIMAL (9,4) NULL,
[Opłata dystrybucyjna zmienna] DECIMAL (9,4) NULL,
[Stawka jednostkowa energii] AS CAST (([Energia elektryczna czynna] + [Opłata dystrybucyjna zmienna])*('1.' + dbo.fnVAT(ID)) AS DECIMAL (9,4)) PERSISTED NOT NULL
)
VAT table:
CREATE TABLE dbo.VAT
(
ID INT NOT NULL IDENTITY (1,1),
Data DATE NOT NULL,
Stawka SMALLINT NOT NULL,
);
Scalar function:
CREATE FUNCTION fnVAT (@VAT SMALLINT)
RETURNS SMALLINT
AS BEGIN
SELECT @VAT = Stawka FROM VAT AS V JOIN Prąd AS P ON V.ID = P.ID
RETURN @VAT
END
source to share
Since VAT depends on the content of another table, I don't think it is possible to create this column that is saved. This means that every time you select that column, the scalar function will be called separately for each row in the select. It can be really bad for performance and I can't recommend it, but if you want to do it, this is the way to do it:
CREATE FUNCTION fnVAT (@DATE date)
RETURNS decimal(3,2)
AS BEGIN
declare @VAT smallint
SELECT top 1 @VAT = VAT FROM VAT where Date <= @DATE order by DATE desc
RETURN 1 + 0.01 * @VAT
END;
alter table Prad add VAT AS (Value1 + Value2) * dbo.fnVAT(Date)
My example was done using this table:
CREATE TABLE Prad
([ID] int, [Date] date, [Value1] DECIMAL (9,4), [Value2] DECIMAL (9,4))
As I thought it would be much easier for me not to use column names in Poland.
Example in SQL Fiddle
As you said, you can do this in a trigger as well, which should be much better, since then the fetch is only done once and since the VAT doesn't really change, this may often be a better idea.
source to share