Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

Separating Debit and Credit at SQL Query.

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

The result :

TrsId = 'D'

TrsNo  AccNo  Debet     Credit
A         11           100            0
A         22               0        100

TrsId = 'C'

TrsNo  AccNo  Debet     Credit
A         22           100            0
A         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.


0
emi_sastra
Asked:
emi_sastra
  • 6
  • 4
  • 2
1 Solution
 
emi_sastraAuthor Commented:
Sorry, more result.

The result :

TrsId = 'D'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         11           100            0
A         C         22               0        100

TrsId = 'C'

TrsNo  TrsId    AccNo  Debet     Credit
A         D         22           100            0
A         C         11              0         100

If Amount is less than 0 then reverse from Debet to Credit.

How could I do it?

Thank you.
0
 
GarthSnyderCommented:
This schema is a little strange - are you sure you wouldn't be better off with a single (TrsID, AcctA, AcctB, Amount) table?

But assuming you are given this schema... Just use an IF function: IF(Amount < 0, 0, Amount) for the debit and IF(Amount >= 0, Amount, 0) for the credit. That's for the first line of output. Use an analogous pair of IFs for the other account.

This also works in Excel.
0
 
GarthSnyderCommented:
Sorry, the second IF in that example should be IF(Amount < 0, -Amount, 0). But you get the idea.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
emi_sastraAuthor Commented:
Hi Garth,

Those tables are header and detail tables.

Would you please provide more complete sample to get the result, cause I am not familiar with it?

Thank you.
0
 
GarthSnyderCommented:
If you can post the original SQL that generates your output, I will update it with the IF expressions.
0
 
emi_sastraAuthor Commented:
The problem is I have idea of doing it, I never do it before querying like it.

Just do if function is simple, but doing the query for different result from 2 tables is different story.

Thank you.
0
 
GarthSnyderCommented:
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
0
 
emi_sastraAuthor Commented:
I'm afraid you'll have to be more specific about what you need. You will not get the output you sketched out above (with TrsID labels for each case and separate headers per transaction) from a single query. This is more in the domain of reporting than a database query.
It is possible, I know the powerful of query, not no need using reporting.

Whatever query generates the fields marked "debit" and "credit", just use the IF() expression in both of those to put the amount value in the proper column.
Yes, using If function but not as simple as IF(Amount < 0, 0, Amount).

Thank you.


0
 
SharathData EngineerCommented:
Can you check this.
select A.TrsNo,'D' as TrsId ,
       case when A.TrsId = 'D' then A.AccNoA else B.AccNoB end as AccNo,
       case when B.Amount>0 then B.Amount else 0 end as Debit,
       case when B.Amount<0 then B.Amount else 0 end as Credit
  from TableA A join TableB B on A.TrsNo = B.TrsNo
 union all
select A.TrsNo,'C' as TrsId ,
       case when A.TrsId = 'C' then A.AccNoA else B.AccNoB end as AccNo,
       case when B.Amount<0 then B.Amount else 0 end as Debit,
       case when B.Amount>0 then B.Amount else 0 end as Credit
  from TableA A join TableB B on A.TrsNo = B.TrsNo

Open in new window

0
 
emi_sastraAuthor Commented:
Hi Sharath,

Let me try first.

My real table structure is different from the sample I provide, just to make it simple.

Thank you.
0
 
emi_sastraAuthor Commented:
Hi Sharath,

That's great, just one shot for a single record at TableB.

I will open another question for multiple record at TableB.

Thank you very much for your help
   
   

0
 
SharathData EngineerCommented:
You are welcome. sure, you can open another question and provide the link
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now