Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
9
375 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 41

Accepted Solution

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

719 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