CODEWITHSUNDEEP
sqlsql-server-2008
Only Bolivian Here
Only Bolivian Here

Reputation: 36743

Creating a calculated field for my simple SQL 2008 table

Here's what the table looks like:

create table Shipping
(
ShippingId int primary key identity(1,1),
ProductId int foreign key references Product(ProductId),
LargoEnCm decimal(16,2),
AltoEnCm decimal(16,2),
AnchoEnCm decimal(16,2),
PesoKg decimal(16,2),
PesoVolumen decimal(16,2),
PesoFacturable decimal(16,2),
A decimal(16,2),
B decimal(16,2),
C decimal(16,2),
D decimal(16,2),
E decimal(16,2),
F decimal(16,2),
G decimal(16,2)
)

For example, the field PesoVolumen should be a calculated field from this:

(LargoEnCm * AltoEnCm * AnchoEnCm) / 6000

How would I declare this calculation so that everytime someone enters the data in the rows this field is automatically populated?

How about conditional statements? Does SQL support this?

For example, in PesoFacturable the value of either PesoKg or PesoVolumen would go into it, depending on which is bigger. An If statement would solve this, can I write this directly in SQL?

Upvotes: 1

Views: 5038

Answers (2)

Tom Hunter
Tom Hunter

Reputation: 5918

CREATE TABLE [dbo].[Shipping]
(
    [ShippingId]        INT PRIMARY KEY IDENTITY(1,1),
    [ProductId]         INT FOREIGN KEY REFERENCES [dbo].[Product]([ProductId]),
    [LargoEnCm]         DECIMAL(16,2),
    [AltoEnCm]          DECIMAL(16,2),
    [AnchoEnCm]         DECIMAL(16,2),
    [PesoKg]            DECIMAL(16,2),
    [PesoFacturable]    AS CASE WHEN [PesoKg] > (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000) THEN [PesoKg] ELSE (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000) END,
    [A]                 DECIMAL(16,2),
    [B]                 DECIMAL(16,2),
    [C]                 DECIMAL(16,2),
    [D]                 DECIMAL(16,2),
    [E]                 DECIMAL(16,2),
    [F]                 DECIMAL(16,2),
    [G]                 DECIMAL(16,2),
    [PesoVolumen]       AS (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000)
)

Upvotes: 1

Tim Rogers
Tim Rogers

Reputation: 21713

Look at the documentation for computed columns. You need something like this:

create table Shipping
(
ShippingId int primary key identity(1,1),
ProductId int foreign key references Product(ProductId),
LargoEnCm decimal(16,2),
AltoEnCm decimal(16,2),
AnchoEnCm decimal(16,2),
PesoKg decimal(16,2),
PesoFacturable decimal(16,2),
A decimal(16,2),
B decimal(16,2),
C decimal(16,2),
D decimal(16,2),
E decimal(16,2),
F decimal(16,2),
G decimal(16,2),
PesoVolumen AS (LargoEnCm * AltoEnCm * AnchoEnCm) / 6000
)

You can include most expressions as a computed column calculation, including CASE statements.

Upvotes: 2

Related Questions