asked on

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.

nmcdermaid

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?

emi_sastra

ASKER

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.

Sharath S

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

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi Sharath,

Let me try first.

Thank you.

emi_sastra

ASKER

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.

Sharath S

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
*/

Open in new window

emi_sastra

ASKER

Hi Sharath,

My mistakes.

I works excellent

Thank you very much for your help.

Sharath S

you are welcome :)