If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Case statement? or is it easier

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 4
Case statement? or is it easier

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!!!
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
you're right, CASE expression

along with SUM and GROUP BY

when's this assignment due?
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 4
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.....
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
show me how you handle the deposit lines, and i'll show you where to stick the minus sign
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 4
I think I got it!!!

CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
where's the SUM()? and why divide by 100?
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Quote:
Originally Posted by belmontiz View Post
The table stores the data all in prime numbers
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.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 4
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?
Reply With Quote
  #9 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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 --
Code:
, SUM( CASE WHEN t.transactiontype = 'DEPOSIT' 
            THEN t.total
            ELSE NULL END) / 100 AS total_dep
, SUM( CASE WHEN t.transactiontype = 'DISPENSE' 
            THEN - t.total
            ELSE NULL END) / 100 AS total_disp
, SUM( t.total ) AS total_total
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Location: Russia. Kursk
Posts: 3
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On