Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
9
371 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
 
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
3 Use Cases for Connected Systems

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

 
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

3 Use Cases for Connected Systems

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 53
sql query help 2 44
How to return more than 1 value from a store procedure 21 37
Access 2010 Query Syntax 5 22
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now