Working with money

Seppuku · #1 (**permalink**) 06-01-04, 13:31

I'm dividing a monitary value into two monitary values, which need to sum up to the original value. I have written some SQL to do this, but just want to double check that I'm doing this efficiently:

In this example, the dollar amount is $102.33 (eventually, this value will be variable). It should divide into 51.17, and 51.16 (which it does). Although this accomplishes the goal, is it the correct way?

Code:

SELECT Round((102.33/2), 2) AS Half, 102.33-Round((102.33/2), 2) AS Half2

Thanks!

Pat Phelan · #2 (**permalink**) 06-02-04, 00:28

Just being an outright purist, I'd use something like:

Code:

SELECT whole, half, whole - half
   FROM (SELECT whole, Round(whole / 2, 2) AS half
      FROM (SELECT 102.33 AS whole) AS a) AS b

It does exactly the same thing that your example did, it just keeps one thing in one place instead of requiring a lot of jockeying to keep things consistent.

-PatP

Seppuku · #3 (**permalink**) 06-08-04, 18:24

Sorry for the delay in getting back on this.. got side tracked on another project.. but here we go!

Alright.. that does work, except that when I attempt to assign the value of half and whole-half to a variable, I get the error "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Ex:

Code:

SELECT whole, @half = half, @half2 = (whole - half)
   FROM (SELECT whole, Round(whole / 2, 2) AS half
      FROM (SELECT 102.33 AS whole) AS a) AS b

Pat Phelan · #4 (**permalink**) 06-08-04, 22:10

You can't select a rowset and make an assignment in a single SELECT statement. You probably want something more like:

Code:

SELECT @whole = whole, @half = half, @half2 = (whole - half)
   FROM (SELECT whole, Round(whole / 2, 2) AS half
      FROM (SELECT 102.33 AS whole) AS a) AS b

This will assign the rowset values to the variables instead of trying to do a mix-n-match in a single SELECT.

-PatP

Seppuku · #5 (**permalink**) 06-09-04, 10:42

Ah.. gotcha.. thx Pat

Internet Services

Web Development

Digital Marketing

Consumer Tech