Math function question

Question

Post reply

Math function question

brian.cook

Default port

Points: 1484
More actions
July 22, 2010 at 8:41 am

#227115

I have four fields that are Money type.
How would I get the result of the three of the fields into the fourth field?
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

so you have three fields that need to be summed in the fourth field?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

brian.cook Default port Points: 1484 More actions · Answer 2

Correct. Would I use the same function as in Excel? Or something similar?

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

yeah! basically you can do it one of two ways. if you need to store this data then you need to think about how the data gets in the table. you may need a trigger or some mechanism that updates the fourth field but you can also grab it directly in the select. If you do this in a selct it would look like the following.

select col1,col2,col3,(col1+col2+col3) as col4

you could use the same logic in an update

set col4=(col1+col2+col3)

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

brian.cook Default port Points: 1484 More actions · Answer 4

Thanks, I knew it would be something simple!

I appreciate the help. That was the last item I was trying to figure out.

Have a great day!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.

How about you just tell us what you'd use, Joe. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brian.cook Default port Points: 1484 More actions · Answer 6

With that said, what would you use?

I inherrited the database from someone else. I am cleaning up what I can, and enhancing where it needs to be.

So, what is your suggestion?

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Dan.Humphries SSChampion Points: 12125 More actions · Answer 7

Here is a link to an article I read some time back that has some pretty decent information on the subject.

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/27/performance-storage-comparisons-money-vs-decimal.aspx

What the autor hints at though is that this is one of many examples in the IT industry where your personal belief about something becomes die hard fact. There are in my estimation very strong arguments on both sides of the fense and this article points out a few arguments on each side. you must decide what to do based on the argument you feal is better.

To quote a great movie, in the service you must choose between the lesser of two weevles.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

brian.cook Default port Points: 1484 More actions · Answer 8

Thanks Dan. I will look it over.

Brian

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.

Care to elucidate? Seems if you are going to say something like this, you really should support it instead of tell people to "Google it."

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jeff Moden (7/22/2010)
Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.
How about you just tell us what you'd use, Joe. 😉

This also comes down to context. There's an implicit definition of "correct" which could use being brought out (As in - "not compliant with GAAP standards" does not necessarily mean universally incorrect, especially given GAAP's upcoming demise....).

Depending on the use cases you need - Money may or may not suit your needs.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

brian.cook Default port Points: 1484 More actions · Answer 11

Matt Miller (#4) (7/22/2010)
Jeff Moden (7/22/2010)
Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.
How about you just tell us what you'd use, Joe. 😉
This also comes down to context. There's an implicit definition of "correct" which could use being brought out (As in - "not compliant with GAAP standards" does not necessarily mean universally incorrect, especially given GAAP's upcoming demise....).
Depending on the use cases you need - Money may or may not suit your needs.

In respect to this project, the fields are for $ expended or budgeted for projects.

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.

I would be interested in seeing some code example of these problems or links to examples.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

brian.cook (7/22/2010)
Matt Miller (#4) (7/22/2010)
Jeff Moden (7/22/2010)
Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.
How about you just tell us what you'd use, Joe. 😉
This also comes down to context. There's an implicit definition of "correct" which could use being brought out (As in - "not compliant with GAAP standards" does not necessarily mean universally incorrect, especially given GAAP's upcoming demise....).
Depending on the use cases you need - Money may or may not suit your needs.
In respect to this project, the fields are for $ expended or budgeted for projects.

So - the "weaknesses" often brought up about money is that is it a precise data type, so it will truncate after a certain point (4 decimal places). If you're doing complicated math, or multiplying and dividing over and over you may end up with different results if you used a FLOAT rather than a MONEY column, during to the digits being truncated off.

If you have some gruesome allocation function (splitting for example IT's budget into sub-departments, and then prorating to a day rate), you could end up with dicrepancies. If on the other hand, you're dealing in + and -, you may find that moeny is actually a whole lot less trouble (since it will truncate for you).

Just be aware of each of the data types, what they do, and how they do it in regards to your operation. It will depend (as the saying always seems to be) on what you want, and what the users of said numbers expect.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

brian.cook Default port Points: 1484 More actions · Answer 14

Matt Miller (#4) (7/22/2010)
brian.cook (7/22/2010)
Matt Miller (#4) (7/22/2010)
Jeff Moden (7/22/2010)
Joe Celko (7/22/2010)
brian.cook (7/22/2010)
Thanks, I knew it would be something simple!
Actually, using the proprietary MONEY is a huge problem. It does not divide or multiply correctly. It does not conform to EU and GAAP rules about calculations. Google it.
How about you just tell us what you'd use, Joe. 😉
This also comes down to context. There's an implicit definition of "correct" which could use being brought out (As in - "not compliant with GAAP standards" does not necessarily mean universally incorrect, especially given GAAP's upcoming demise....).
Depending on the use cases you need - Money may or may not suit your needs.
In respect to this project, the fields are for $ expended or budgeted for projects.
So - the "weaknesses" often brought up about money is that is it a precise data type, so it will truncate after a certain point (4 decimal places). If you're doing complicated math, or multiplying and dividing over and over you may end up with different results if you used a FLOAT rather than a MONEY column, during to the digits being truncated off.
If you have some gruesome allocation function (splitting for example IT's budget into sub-departments, and then prorating to a day rate), you could end up with dicrepancies. If on the other hand, you're dealing in + and -, you may find that moeny is actually a whole lot less trouble (since it will truncate for you).
Just be aware of each of the data types, what they do, and how they do it in regards to your operation. It will depend (as the saying always seems to be) on what you want, and what the users of said numbers expect.

Good advice Matt. This project is real simple. Total three columns of each row into a fourth column. No massive divisions, multiplication, etc.

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner