1. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721

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!

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721
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```

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

5. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721
Ah.. gotcha.. thx Pat

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•