Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: Working with money

    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!
    That which does not kill me postpones the inevitable.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #3
    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
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ah.. gotcha.. thx Pat
    That which does not kill me postpones the inevitable.

Posting Permissions

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