Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 380

# Separating Debit and Credit at SQL Query.

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
emi_sastra
• 4
• 4
1 Solution

Commented:
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

Author Commented:
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

Data EngineerCommented:
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

Data EngineerCommented:
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

Author Commented:
Hi Sharath,

Let me try first.

Thank you.
0

Author Commented:
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

Data EngineerCommented:
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

Author Commented:
Hi Sharath,

My mistakes.

I works excellent

Thank you very much for your help.
0

Data EngineerCommented:
you are welcome :)
0

## Featured Post

• 4
• 4
Tackle projects and never again get stuck behind a technical roadblock.