SQL Table that calculates values from another SQL table

2018-06-23 09:34:25

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

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

链接地址: http://www.djcxy.com/p/65606.html

上一篇: 如何在mySQL中使用case来编写查询

下一篇: SQL表，用于计算另一个SQL表中的值