Create a new column with an Expression — Parv The IT Geek

Sometimes there may be a requirement to create a new column based upon existing columns with a minor manipulation.

For example in the AdventureWorks database the table TransactionHistoryArchive shows the quantity and the actual cost of each unit but not the total value of the order.

That would be achieved by multiplying the Quantity and AcutalCost columns.

Current results from the table are below.

SELECT [TransactionID] ,[ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionDate] ,[TransactionType] ,[Quantity] ,[ActualCost] ,[ModifiedDate] 
FROM [AdventureWorks].[Production].[TransactionHistoryArchive]

However we need to multiply Quantity * ActualCost and call the column TotalOrderValue.

SELECT [TransactionID] ,[ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionDate] ,[TransactionType] ,[Quantity] ,[ActualCost] ,[ModifiedDate] ,[Quantity] * [ActualCost] AS TotalOrderValue 
FROM [AdventureWorks].[Production].[TransactionHistoryArchive]

Originally published at https://parvtheitgeek.com on January 6, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: