Results 1 to 7 of 7

Thread: Query problem

  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Unanswered: Query problem

    Hi,

    I've got the following problem:

    In a table named "Commission" are all commissions of sales people per month listed.
    Now I have to calculate and update the following:
    I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

    I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

    Has any brain a solution for me available ???

    Thx a lot

    dajm

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    Summarizing Data Using COMPUTE and COMPUTE BY

    In books online

    Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.

  3. #3
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Originally posted by HanafiH
    Summarizing Data Using COMPUTE and COMPUTE BY

    In books online

    Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.
    Sorry, but COMPUTE is not helping me as I have to compare the result with `0`and to update the same fact table . Any ideas ?

    Thx.

  4. #4
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72

    Re: Query problem

    use a cursor

    Originally posted by dajm
    Hi,

    I've got the following problem:

    In a table named "Commission" are all commissions of sales people per month listed.
    Now I have to calculate and update the following:
    I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

    I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

    Has any brain a solution for me available ???

    Thx a lot

    dajm

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    set based solutions are almost always better than cursor solutions

    update the Commission table for each month/salesman where total commissions for the month are negative:
    Code:
    update Commission
       set commissions = 0
      from Commission as table1
    inner
      join (
           select themonth
                , salesmanID
             from Commission
           group
               by themonth
                , salesmanID
           having sum(commissions) < 0
           ) as table2
        on table1.themonth = table2.themonth
       and table1.salesmanID = table2.salesmanID
    (caution: untested, but it should work)

    rudy
    http://r937.com/

  6. #6
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1

    Did u try CASE statement ?

    try CASE along with COMPUTE statement.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  7. #7
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Super solution. Thx a lot...

    dajm


    Originally posted by r937
    set based solutions are almost always better than cursor solutions

    update the Commission table for each month/salesman where total commissions for the month are negative:
    Code:
    update Commission
       set commissions = 0
      from Commission as table1
    inner
      join (
           select themonth
                , salesmanID
             from Commission
           group
               by themonth
                , salesmanID
           having sum(commissions) < 0
           ) as table2
        on table1.themonth = table2.themonth
       and table1.salesmanID = table2.salesmanID
    (caution: untested, but it should work)

    rudy
    http://r937.com/

Posting Permissions

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