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
Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
9
373 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
  • 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 40

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 40

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 40

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 40

Expert Comment

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

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help on t-sql 2012 10 56
SQL Server 2012 r2 - Make Temp Table Query Faster 5 50
How to count the days a record spends in a step 21 51
Trouble with <> 2 20
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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