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
– Marcus Rickert, Commented Dec 4, 2013 at 12:23
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
– sephiroth85, Commented Dec 5, 2013 at 14:15

SAS · Accepted Answer · 2013-12-09 06:47:06Z

0

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

edited Dec 9, 2013 at 6:47

answered Dec 4, 2013 at 12:20

SAS

4,0852 gold badges33 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sephiroth85 Over a year ago

creating a view would be a good option although how do i tell it to take columns from and table and then multiply it by certain value

SAS Over a year ago

If I understand you correctly, then someting like tihs, which combines data from two tables: CREATE VIEW vwMyView AS SELECT a.col1, b.col1*2 FROM MyTbl1 AS a INNER JOIN MyTbl2 AS b ON a.id = b.Id

sephiroth85 Over a year ago

I managed to get in in my table by doing this ALTER TABLE myTableYear ADD [MonthlyValue] AS [Month number in cases]*[price of product]

sephiroth85 Over a year ago

but instead of having it in same table i would like to sql to create a new view with those details

SAS Over a year ago

OK, sorry if I don't understand fully, but are you perhaps looking for this: <br/> CREATE VIEW vwTableYear AS SELECT [Month number in cases]*[price of product] AS MonthlyValue FROM myTableYear <br/> Then you could access the value through the view. Just add more columns to the view as neccesary. <br/> I hope that helps you!

Collectives™ on Stack Overflow

SQL Table that calculates values from another SQL table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related