?
Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
9
Medium Priority
?
377 Views
Last Modified: 2012-05-06
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
Comment
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
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
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

by:emi_sastra
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

by:Sharath
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

Open in new window

0
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 41

Accepted Solution

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

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24104340
Hi Sharath,

Let me try first.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
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

by:Sharath
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
*/

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24104425
Hi Sharath,

My mistakes.

I works excellent

Thank you very much for your help.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24104560
you are welcome :)
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
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

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question