Select data with the different filters in sql server

Question

I have this table named expenses with the fallowing fields refnum, transdate, transtype, transcategory, amount

this table has the following data

refnum | transdate  | transtype | transcategory |     amount 
 1     | 10/10/2015 |  cash-out |     null      |    5000.00 
 1     | 10/10/2015 |  cash-in  |     null      |    3000.00 
 1     | 10/10/2015 |  cash-in  |     null      |    1000.00 
 2     | 10/10/2015 |  cash-out |     null      |    5000.00 
 2     | 10/10/2015 |  cash-in  |     null      |    3000.00 
 2     | 10/10/2015 |  cash-in  |     null      |    2000.00
 3     | 10/10/2015 |  cash-out |     null      |    5000.00
 3     | 10/10/2015 |  cash-in  |     null      |    5000.00

The question is: Is it possible to get the balance of the amount base on its transtype and refnum

I have this query:

SELECT 
    A.refnum, A.transtype, A.amount,(A.amount-B.amount) as balance 
FROM 
    expenses AS A 
INNER JOIN
    expenses AS B ON A.refnum = B.refnum 
WHERE
    A.transtype = 'cash-in' 
    AND B.transtype = 'cash-out' 
    AND (A.amount - B.amount) > 0

I am confused, this query I think is good if cash-in is exactly the same as the amount of cash-out but the problem is almost all cash-in is not the same amount of cash-out, hope you guys understand what i am asking for.

I need the query that will show only with the refnum of 1.

Is it possible?

something like SELECT refnum, SUM(CASE WHEN transtype='cash-out' then -amount ELSE amount END)) from expenses GROUP BY refnum — Nick.Mc
– Nick.Mc, Commented Aug 23, 2015 at 2:49
Hello pretty welcome to StackOverflow, next time try to provide a SqlFiddle so we can help you faster and better – Also please read How to ask And for sql questions please include your rdbms and data sample with input and desire output. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 23, 2015 at 2:57

Community · Accepted Answer · 2017-05-23 12:29:43Z

1

Just like Nick say:

Using conditional with aggregated methods

another example

SQL Fiddle Demo

group by will separate your data into groups, in this case a group for each refnum (1,2,3) GROUP BY
conditional SUM: A normal SUM(amount) will give you that, the SUM of the whole group you define above. But not all amount are positive. So you use the CASE statement to change amount to -amount WHEN transtype='cash-out'

.

SELECT 
    refnum, 
    SUM(CASE 
            WHEN transtype='cash-out' then -amount 
            ELSE amount 
        END)  as total
FROM expenses 
GROUP BY refnum

Edit

For your reference this is a way using CTE and Join

CTE or Common Table Expressions: Allow you create subqueries so you can use it on other queries. Works like creating a temporal table so you can do select or join over that result.
In this case I create two table cash_out and cash_in each one contain the value of the sum for each refnum.
After I wrote it i realize if one refnum doesn't have cash_in or cash_out this won't work. So I add another subquery to include all the refnum and perform a left join that way if not data the join will still work and add 0.
ISNULL(variable,0): return variable if variable <> null or 0 otherwise

SQL Fiddle Demo

WITH ref as (
    SELECT DISTINCT refnum
    FROM expenses
),
cash_out as (    
    SELECT A.refnum, SUM(A.amount) balance 
    FROM 
        expenses AS A 
        where A.transtype = 'cash-out'
    GROUP BY A.refnum
),
cash_in as (
    SELECT A.refnum, SUM(A.amount) balance 
    FROM 
        expenses AS A 
        where A.transtype = 'cash-in' 
    GROUP BY A.refnum
)
SELECT cin.refnum, (ISNULL(cin.balance,0) - ISNULL(cout.balance,0)) as total
FROM 
   ref r LEFT JOIN
   cash_in cin on r.refnum = cin.refnum LEFT JOIN
   cash_out cout on r.refnum = cout.refnum

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 23, 2015 at 3:15

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Juan Carlos Oropeza Over a year ago

For you I include an example using CTE and Join. Same results but need more work. I probably did it that way if didnt know conditional agregation. One important step you miss was group by

pretty me Over a year ago

Your answer @Juan Carlos Oropeza works perfectly but don't know how it works. i am not satisfied if i don't know how it works. do you have the link that explain that lines of codes? sorry, i am new in terms of programming. i just want to explore and study more about sql. i really appreciate your effort..

pretty me Over a year ago

and by the way how can i turn the result into possitive?

Juan Carlos Oropeza Over a year ago

I update my answer and my fiddle with more explain. Let me know if still are something you don't understand.

Juan Carlos Oropeza Over a year ago

Don't understand your question about result possitive show me an example.

|

Collectives™ on Stack Overflow

Select data with the different filters in sql server

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related