I am pulling data from a very simple database. It is for cash dispursment and collection. The table stores the data all in prime numbers, with a field differentiating between transaction type (deposit or dispense).

I need to build a simple report to show how much the user dropped in comparison to what was dispensed, but i've hit a brain block.

here's an example of the table:

12 Karen Dispense 1500
12 Karen deposit 1500
26 Tony Dispense 600
26 Tony Deposit 605

What i need is to get:

12 Karen 0
26 Tony 5

I keep trying to overcomplicate it, but i'm sure you are much smarter then me and have a much simpler way to do this that i am missing....

Thanks in advance!!!

you're right, CASE expression

along with SUM and GROUP BY

when's this assignment due?

I apologize if this is a dumb question, but how do i get the 'Dispense' lines to be negative? That is the piece I am stuck at.....

show me how you handle the deposit lines, and i'll show you where to stick the minus sign

I think I got it!!!

CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop

where's the SUM()? and why divide by 100?

You lost me there, bro'.....

But what you need is a CASE statement to determine whether to multiply the amount by -1, and then just sum up the values.

I should probably post this as a new thread...but i've gotten pretty close. I'm having an issue with having 2 joins and it messing up the Sum. Here is the query as i wrote it out:

select t.userUID, u.FirstName, u.LastName, b.bankname,
SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop
from kctb_admin_transactions t
join kctb_admin_users u on t.userUID = u.userUID
join kctb_admin_banknames b on t.banknameUID = b.banknameUID
where t.dtstamp > '11-14-2012' and t.dtstamp < '11-15-2012'
group by t.userUID, u.FirstName, u.LastName, b.bankname
order by t.userUID, u.FirstName, u.LastName, b.bankname

But it's giving me the wrong number in the sum. It seems to be doubling up the negative amount. So then I was trying to do something like this....

select t.userUID, u.FirstName, u.LastName,
(Select b.bankname from kctb_admin_banknames b where t.banknameUID = b.banknameUID) ,
SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop
from kctb_admin_transactions t
join kctb_admin_users u on t.userUID = u.userUID
where t.dtstamp > '11-14-2012' and t.dtstamp < '11-15-2012'
group by t.userUID, u.FirstName, u.LastName, t.banknameUID
order by t.userUID, u.FirstName, u.LastName, t.banknameUID

But it's obviously not working. I'm not sure the best way to put the imbedded Select statement in there with a join, and get it to group/sum....

thoughts?

if you're tracking down incorrect totals, try splitting off the different ones separately

change this --

SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop

to this --

select t.userUID, (sum(t.total) - 2*sum(tn.total))/100
from
kctb_admin_transactions t
left join
kctb_admin_transactions tn
on t.transactionsId = tn.transactionsId
amd t.dtstamp > '11-14-2012' and t.dtstamp < '11-15-2012'
and tn.dtstamp > '11-14-2012' and tn.dtstamp < '11-15-2012'
and tn.transactiontype = 'DISPENSE'
group by t.userUID