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
  • Last Modified:

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
Asked:
emi_sastra
  • 4
  • 4
1 Solution
 
nmcdermaidCommented:
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
 
emi_sastraAuthor 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
 
SharathData 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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SharathData 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

Open in new window

0
 
emi_sastraAuthor Commented:
Hi Sharath,

Let me try first.

Thank you.
0
 
emi_sastraAuthor 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
 
SharathData 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
*/

Open in new window

0
 
emi_sastraAuthor Commented:
Hi Sharath,

My mistakes.

I works excellent

Thank you very much for your help.
0
 
SharathData EngineerCommented:
you are welcome :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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