Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
522 Views
Hi,

I have 2 tables structures:

Table One (A)
1. TrsNo.
2. AccNoA.
3. TrsId (D/C).

Tables Two (B).
1. TrsNo.
2. AccNoB.
3. Amount (-1000, 1000)

i.e.

Table One                                Table Two

TrsNo  AccNoA   TrsId               TrsNo   AccNoB  Amount
A         11           D                     A         22         100

The result :

TrsId = 'D'

TrsNo  AccNo  Debet     Credit
A         11           100            0
A         22               0        100

TrsId = 'C'

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.

0
Question by:emi_sastra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4
• 2

LVL 1

Author Comment

ID: 24103451
Sorry, more result.

The result :

TrsId = 'D'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         11           100            0
A         C         22               0        100

TrsId = 'C'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         22           100            0
A         C         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.
0

LVL 3

Expert Comment

ID: 24103481
This schema is a little strange - are you sure you wouldn't be better off with a single (TrsID, AcctA, AcctB, Amount) table?

But assuming you are given this schema... Just use an IF function: IF(Amount < 0, 0, Amount) for the debit and IF(Amount >= 0, Amount, 0) for the credit. That's for the first line of output. Use an analogous pair of IFs for the other account.

This also works in Excel.
0

LVL 3

Expert Comment

ID: 24103488
Sorry, the second IF in that example should be IF(Amount < 0, -Amount, 0). But you get the idea.
0

LVL 1

Author Comment

ID: 24103511
Hi Garth,

Those tables are header and detail tables.

Would you please provide more complete sample to get the result, cause I am not familiar with it?

Thank you.
0

LVL 3

Expert Comment

ID: 24103549
If you can post the original SQL that generates your output, I will update it with the IF expressions.
0

LVL 1

Author Comment

ID: 24103564
The problem is I have idea of doing it, I never do it before querying like it.

Just do if function is simple, but doing the query for different result from 2 tables is different story.

Thank you.
0

LVL 3

Expert Comment

ID: 24103645
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
0

LVL 1

Author Comment

ID: 24103675
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.
It is possible, I know the powerful of query, not no need using reporting.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
Yes, using If function but not as simple as IF(Amount < 0, 0, Amount).

Thank you.

0

LVL 41

Accepted Solution

Sharath earned 500 total points
ID: 24103703
Can you check this.
select A.TrsNo,'D' as TrsId ,
case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
case when B.Amount>0 then B.Amount else 0 end as Debit,
case when B.Amount<0 then B.Amount else 0 end as Credit
from TableA A join TableB B on A.TrsNo = B.TrsNo
union all
select A.TrsNo,'C' as TrsId ,
case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
case when B.Amount<0 then B.Amount else 0 end as Debit,
case when B.Amount>0 then B.Amount else 0 end as Credit
from TableA A join TableB B on A.TrsNo = B.TrsNo
0

LVL 1

Author Comment

ID: 24103720
Hi Sharath,

Let me try first.

My real table structure is different from the sample I provide, just to make it simple.

Thank you.
0

LVL 1

Author Comment

ID: 24103836
Hi Sharath,

That's great, just one shot for a single record at TableB.

I will open another question for multiple record at TableB.

Thank you very much for your help

0

LVL 41

Expert Comment

ID: 24103842
You are welcome. sure, you can open another question and provide the link
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.ā
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cuā¦
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll