Results 1 to 10 of 10
  1. #1
    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!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    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.....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where's the SUM()? and why divide by 100?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  8. #8
    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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  10. #10
    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

Posting Permissions

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