[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
Medium Priority
378 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
A         33         200

The result :

TrsId = 'D'

TrsNo  AccNo  Debet     Credit
A         11           300            0
A         22               0        100
A         33               0        200

TrsId = 'C'

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         33           200            0
A         11              0         300

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
• 4
• 4

LVL 30

Expert Comment

ID: 24103939
Can TrsID in Table 1 only ever be C or D (credit or Debit)
For your second example, do you mean that if TrsID was actually C (rather than D) then that is what you want to see?
0

LVL 1

Author Comment

ID: 24103950
Hi nmcdermaid,

Can TrsID in Table 1 only ever be C or D (credit or Debit)
Yes.

For your second example, do you mean that if TrsID was actually C (rather than D) then that is what you want to see?
Could be both. I provide sample result for TrsId ="C" or TrsId ="D"

Thank you.
0

LVL 41

Expert Comment

ID: 24104054
check this
``````select A.TrsNo,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount>0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount<0 then B.Amount else 0 end) as Credit
from TableA A join TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end
union all
select A.TrsNo,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount<0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount>0 then B.Amount else 0 end) as Credit
from TableA A join TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end
``````
0

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 24104065
If you want the TrsId also in the result, try this
``````select A.TrsNo,'D' as TrsId ,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount>0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount<0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end
union all
select A.TrsNo,'C' as TrsId ,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount<0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount>0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end
``````
0

LVL 1

Author Comment

ID: 24104340
Hi Sharath,

Let me try first.

Thank you.
0

LVL 1

Author Comment

ID: 24104397
Hi Sharath,

I have tried, for transaction with TrsId = 'D', it works.

for transaction with TrsId = 'C', there are 4 records where 2 D and 2 C with identical amount :

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         33           200            0
A         22               0        100
A         33               0        200

Should be:

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         33           200            0
A         11              0         300

Thank you.

0

LVL 41

Expert Comment

ID: 24104421
I am getting correct result with your sample set. Can you provide your sample set for which you are getting incorrect result.
``````create table #TableA(TrsNo varchar(10),AccNoA int,TrsId varchar(10))
create table #TableB(TrsNo varchar(10),AccNoB int,Amount int)

insert into #TableA values ('A',11,'D')
insert into #TableB values ('A',22,100)
insert into #TableB values ('A',33,200)

select A.TrsNo,'D' as TrsId ,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount>0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount<0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end
union all
select A.TrsNo,'C' as TrsId ,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount<0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount>0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end
/*
TrsNo	TrsId	AccNo	Debit	Credit
A	D	11	300	0
A	C	22	0	100
A	C	33	0	200
*/
truncate table #TableA
truncate table #TableB

insert into #TableA values ('A',11,'C')
insert into #TableB values ('A',22,100)
insert into #TableB values ('A',33,200)

select A.TrsNo,'D' as TrsId ,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount>0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount<0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end
union all
select A.TrsNo,'C' as TrsId ,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
sum(case when B.Amount<0 then B.Amount else 0 end) as Debit,
sum(case when B.Amount>0 then B.Amount else 0 end) as Credit
from #TableA A join #TableB B on A.TrsNo = B.TrsNo
group by A.TrsNo,case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end
/*
TrsNo	TrsId	AccNo	Debit	Credit
A	D	22	100	0
A	D	33	200	0
A	C	11	0	300
*/
``````
0

LVL 1

Author Comment

ID: 24104425
Hi Sharath,

My mistakes.

I works excellent

Thank you very much for your help.
0

LVL 41

Expert Comment

ID: 24104560
you are welcome :)
0

## Featured Post

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
###### Suggested Courses
Course of the Month12 days, 19 hours left to enroll