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

dajmSummarizing 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.|||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.|||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|||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: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/|||try CASE along with COMPUTE statement.|||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: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/