a query

hello all,

im trying to build a query...

the target of this query is to show the sales man, and the balance of the salesman of credits of a single plan.

the data is stored in the table as following,

RowID,SalesManID,PlanID,Credit,TransactionType

the balanace of of a salesman of a single plan is

the (sum(credit) where Transactiontype = 0 ) - (sum(credit) where transaction = 1 )

ofcourse for every single plan and sales man...

please if u need any question to have more detials just ask

and thank u for ur careTry this:

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

|||thank you

its a great way

but mmm

it seems that it is not working

as if all of them has transaction type 0, but in the database there is 0 and 1

so is there anything i can do|||Did you get the minus sign in from of the second condition?

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN-credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

If it still doesn't work then try this as a diagnostic. Just run it in query analyzer to see what is going on. You should just see the unaggregated values with some have positive values corresponding to transactiontype=0 and negative for transactiontype=1.
SELECT PlanID, SalesManID,Transactiontype,
CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END as Balance
From YourTable
</code>