how to learn forumlas - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to learn forumlas

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2005-11-23 : 19:39:15

does anyone know where I can learn how to make forumlas to use in my table fields?

I have two mauals on SQL Server and niether of them cover formulas.

Thanks,
token

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-23 : 21:51:29

SQL Server does not support calculated columns. You will need to do your calculations in a view or stored procedure.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-23 : 22:25:36

are you refering to computed column ?
See Book Online on CREATE TABLE for details
example :

CREATE TABLE mytable 
   (
    low int,
    high int,
    myavg AS (low + high)/2
   )

-----------------
[KH]

token
Posting Yak Master

133 Posts

Posted - 2005-11-24 : 07:26:19

What I mean is that when you are designing a table in EM (similar to Access), you can specify the formula that will be used to populate certain fields.

So for example, if I have the price of a product in FieldA that does not include sales tax, then I simply enter in a formula in FieldB that adds the sales tax rate to the figure in FieldA and enters it into FieldB.

So I don't have to go through each price, work out the sales tax value, and then add it myself manually. I have done this already. It was similar to making a forumla in Excel, but there are some commands that are SQL Server specific I believe. Any idea where I could learn SQL Server formulas?

token
Posting Yak Master

133 Posts

Posted - 2005-11-24 : 07:29:06

This is how a field forumla looks:

(round(([UnitPriceExVat] * 1.175),2))

So what is happening above is that I am adding the sales tax rate of 17.5% to the field "UnitPriceExVat". I am also roudning it off to 2 decimal places.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-24 : 08:47:07

[code]create table tbl
(
UnitPriceExVat numeric(10,2),
UnitPriceVat as round(UnitPriceExVat * 1.175, 2)
)
insert into tbl(UnitPriceExVat) select 10
select * from tbl
[/code]

-----------------
[KH]

token
Posting Yak Master

133 Posts

Posted - 2005-11-24 : 10:05:59

Yes thats the idea khtan...

I am asking if anyone knows where I can LEARN how to make formulas (not SQL).

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-25 : 18:43:09

refer books online in Sql server 2000

Vicky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources