MSDE and Calculated Field?

[Martin Schmid]

I have a table of values... i.e.
[id, qty, price]

I can create a view with an amount, i.e...
[id, qty, price, qty*price]

I want another column that maintains the balance... i.e.
[id, qty, price, qty*price, balance]

A little more info... this is for a stock trading simulation. The initial
balance will be $100,000... each trade needs to decrement (for a BUY) or
increment (for a SELL) the balance. I could maintain a balance table, and
utilize triggers... however, I'd like to show the cumulative effects of each
buy/sell on a report based on the view.

I am using MSDE and VC.Net 7 to create a Web-application.

Thanks for any insight.

 

[Mary Chipman]

The most performant solution would be to create the computed column in
the database table. The values are not stored in the column, but
computed at runtime when you select them. See SQL BooksOnline for
CREATE TABLE syntax for computed columns. You can also create them in
the Enterprise Manager UI.

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[Martin Schmid]

I don't follow... if I have the value for balance of 100000 prior to
'running' the table... where is this stored? And how does each 'row' of the
table increment/decrement this balance? I.e,
if row 1 has qty=5 and price=5, then balance would become 99975.

If row 2 has qty -5 and price 3, balance would be come 99995.

Note that there will be another column for 'team'; each team will start out
with a certain balance, and will have to be adjusted according to their
buy/sell (qty/price) table values.

Thanks,
MS

The most performant solution would be to create the computed column in
the database table. The values are not stored in the column, but
computed at runtime when you select them. See SQL BooksOnline for
CREATE TABLE syntax for computed columns. You can also create them in
the Enterprise Manager UI.

-- Mary
MCW Technologies
http://www.mcwtech.com

I have a table of values... i.e.
[id, qty, price]

I can create a view with an amount, i.e...
[id, qty, price, qty*price]

I want another column that maintains the balance... i.e.
[id, qty, price, qty*price, balance]

A little more info... this is for a stock trading simulation. The initial
balance will be $100,000... each trade needs to decrement (for a BUY) or
increment (for a SELL) the balance. I could maintain a balance table, and
utilize triggers... however, I'd like to show the cumulative effects of each
buy/sell on a report based on the view.

I am using MSDE and VC.Net 7 to create a Web-application.

Thanks for any insight.

 

[Mary Chipman]

Think Excel. You put values in the base columns, and if the values in
those base columns change, the computed column is recalculated when
the row is retrieved at runtime. Think of the expression as being
similar to a formula in an XL spreadsheet. Because this expression is
computed on the server instead of on the client, it's faster. The
result of the expression is not saved in the table, which means your
data is always consistent.

-- Mary
MCW Technologies
http://www.mcwtech.com

I don't follow... if I have the value for balance of 100000 prior to
'running' the table... where is this stored? And how does each 'row' of the
table increment/decrement this balance? I.e,
if row 1 has qty=5 and price=5, then balance would become 99975.

If row 2 has qty -5 and price 3, balance would be come 99995.

Note that there will be another column for 'team'; each team will start out
with a certain balance, and will have to be adjusted according to their
buy/sell (qty/price) table values.

Thanks,
MS

The most performant solution would be to create the computed column in
the database table. The values are not stored in the column, but
computed at runtime when you select them. See SQL BooksOnline for
CREATE TABLE syntax for computed columns. You can also create them in
the Enterprise Manager UI.

-- Mary
MCW Technologies
http://www.mcwtech.com

I have a table of values... i.e.
[id, qty, price]

I can create a view with an amount, i.e...
[id, qty, price, qty*price]

I want another column that maintains the balance... i.e.
[id, qty, price, qty*price, balance]

A little more info... this is for a stock trading simulation. The initial
balance will be $100,000... each trade needs to decrement (for a BUY) or
increment (for a SELL) the balance. I could maintain a balance table, and
utilize triggers... however, I'd like to show the cumulative effects of each
buy/sell on a report based on the view.

I am using MSDE and VC.Net 7 to create a Web-application.

Thanks for any insight.