| Query [message #641875] |
Wed, 26 August 2015 08:16  |
 |
glmjoy
Messages: 187
Registered: September 2011
Location: KR
|
Senior Member |
|
|
I have one column for Debit and Credit. I want to display query Debit - Credit
Here is
My table structure
Create Table FT (Document_No CHAR(5),Document_Type Char (2), Account_Code Char(5),
Amount Number)
insert into FT Values('00001','JV','A0001',7500);
insert into FT Values('00002','JV','A0005',-4500);
insert into FT Values('00001','EI','A0001',-6500);
insert into FT Values('00002','EI','A0005',7500);
I want to Minus Amount Positive as debit and Amount Negative as Credit
Debit - Credit
Select Account_Code, (Amount - Amount) from FT
it gives answer
ACCOU (AMOUNT-AMOUNT)
----- ---------------
A0001 0
A0005 0
A0001 0
A0005 0
But I want Answer
A0001 (7500 - 6500) = 1000
A0001 (7500 - 6500) = 1000
|
|
|
|
| Re: Query [message #641877 is a reply to message #641875] |
Wed, 26 August 2015 08:45  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
What should results be when data is like below?
Create Table FT (Document_No CHAR(5),Document_Type Char (2), Account_Code Char(5),
Amount Number)
insert into FT Values('00001','JV','A0001',7500);
insert into FT Values('00002','JV','A0001',-250);
insert into FT Values('00003','JV','A0001',-200);
insert into FT Values('00004','JV','A0001',-950);
insert into FT Values('00005','JV','A0001',-750);
insert into FT Values('00006','JV','A0001',2500);
|
|
|
|
|
|
|
|
| Re: Query [message #641880 is a reply to message #641877] |
Wed, 26 August 2015 09:03 |
 |
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
|
Senior Member |
|
|
I question your table design. It is not good practice to use the value of the AMOUNT field to determine if it is a Credit or Debit. Debits are not always positive and Credits are not always negative. Your design doesn't allow for this. Plus, what if you want to see ALL Credits? How would you get those from your table? You could use...
WHERE AMOUNT > 0 /* Debits */
but, how can you be sure all positive amounts are Debits? By adding a column to your table, like "AMT_TYPE" and recording if it is a Debit or Credit, you can then join your table with itself to produce the results you want.
Craig...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
