Solved

Separating Debit and Credit at SQL Query.

Posted on 2009-04-08
9
370 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

21 Experts available now in Live!

Get 1:1 Help Now