Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: negative values...

    calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

    Basically in the stored procedure that creates the table the query includes:

    Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
    which is wrong when sale and cost is negative the formula becomes
    (-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....

    but i dont know to write this....any ideas???

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    which is wrong when sale and cost is negative the formula becomes
    (-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....
    How could 'Cost' come negative..?
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajeshpatel
    How could 'Cost' come negative..?
    when the supplier pays you to take his product

    next question: how can sale be 0?

    answer: when you give your product away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds like an absolute value to me.

    Code:
    sale - abs(cost)

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by STUCK1234
    calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

    Basically in the stored procedure that creates the table the query includes:

    Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
    which is wrong when sale and cost is negative the formula becomes
    (-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....

    but i dont know to write this....any ideas???

    Why would it be wrong? Sounds like simple accounting

    My wife say I have a lot of negative values
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2005
    Posts
    76
    I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

    UPDATE SALES
    SET [Profit%] = [Profit%] * -1
    FROM SALES
    WHERE Qty < 0


    So that when the profit% is negative when qty is negative... thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by STUCK1234
    So that when the profit% is negative when qty is negative... thanks
    you're welcome

    you sell negative quantities?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Those are called Returns!
    Inspiration Through Fermentation

  9. #9
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by RedNeckGeek
    Those are called Returns!

    yup

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Our sales monkeys are good at generating negative GP, too!
    Inspiration Through Fermentation

Posting Permissions

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