SQL Table that calculates values from another SQL table

Question

I would like to create an dynamic table that picks up 1st 5 columns from static table and multiply it by a value field.

1. static table (Cases):

Column A; Column B; Column C; Column D; Column E; Value; Jan (cases);...;Dec(cases)

2. Dynamic Table (value * cases by month)

Column A; Column B; Column C; Column D; Column E; Jan (value * cases);...;Dec(value * cases)

In excel i would just time cell by cell next to it. But i want sql to create/update database every time new static file is uploaded.

ok update
after reviewing i need something a bit different, i need to take 2 tables and accumulate them by each other one table would be cases and other in product value
cases table:
Account; Channel; [PROD CODE](this is my p. key); Jan; Feb; Mar; Apr; May; Jun; Jul; Aug; Sep; Oct; Nov; Dec
price table:
Year; SKU (this is my p. key); [Product Name]; Jan; Feb; Mar; Apr; May; Jun; Jul; Aug; Sep; Oct; Nov; Dec;
the number value sits in individual months.
In another words i need
cases table.JAN * price table.Jan where cases table.primary key = price table.primary key

Be careful with your wording. You probably do not want to _create a database` in every case. So you actually have several questions: a) how do you update/create a table from values in another table. b) What is preferable: update or (re-)create. c) How shall this step be triggered? — Marcus Rickert
in terms of update i would get monthly files that need to go into database, then i want to calculate and link it into internal database. Process would be all about receiving data in flat files from managers and uploading them to SQL this is when i would like for all transformations to the data happened — sephiroth85

SAS SAS · Accepted Answer · 2013-12-04T12:20:41

Take a look at computed columns.

Or, if you need to include Another table in the calculation, you could create a user-defined function:

CREATE FUNCTION dbo.GetValue(INT @code, INT @rec)
RETURNS INT
AS ...

Then you would alter your table adding the column:

ALTER TABLE dbo.Mytbl
   ADD MyCalcCol AS dbo.GetValue(CodeVal, RecVal)

Another thought.. why not just create a view, or even an indexed view.

CREATE VIEW vwTableYear AS 
   SELECT [Month number in cases]*[price of product] AS MonthlyValue 
   FROM myTableYear

SQL Table that calculates values from another SQL table

1 Answers