emi_sastra
asked on
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.
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.
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.
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.
Sorry, the second IF in that example should be IF(Amount < 0, -Amount, 0). But you get the idea.
ASKER
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.
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.
If you can post the original SQL that generates your output, I will update it with the IF expressions.
ASKER
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.
Just do if function is simple, but doing the query for different result from 2 tables is different story.
Thank you.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sharath,
Let me try first.
My real table structure is different from the sample I provide, just to make it simple.
Thank you.
Let me try first.
My real table structure is different from the sample I provide, just to make it simple.
Thank you.
ASKER
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
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
You are welcome. sure, you can open another question and provide the link
ASKER
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.