# Thread: Credit / Debit problem

1. Registered User
Join Date
Dec 2004
Posts
78

## Unanswered: Credit / Debit problem

I have a table that has account # and amount columns. All the debit and credit amounts are stored in same column. All debit amounts are positive and credit amounts are negative.
Problem is: I want to write a SQL to list account #, debit and credit in same line and the difference between debit and credit in last column.

For eg: following are 2 rows in table (ACCOUNT|AMOUNT)

A1 \$500
A1 \$-500

I want result as: (ACCOUNT|DEBIT|CREDIT|DIFFERENCE)

A1 \$500 \$-500 \$0

Thanks.

~BS.

2. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
Select A.account,a.amount,b.amount.a.amount - B.amount
From My_table A,
My_table B
Where A.amount > 0
And B.amount < 0
And A.account = B.account
Order By A.account;

3. Registered User
Join Date
Dec 2004
Posts
78
Code:
```Select A.account,sum(a.posted_total_amt),sum(b.posted_total_amt),sum(a.posted_total_amt + B.posted_total_amt)
From ps_ledger A,
ps_ledger B
Where A.posted_total_amt > 0
And B.posted_total_amt < 0
And A.account = B.account
AND A.ACCOUNT = A1001
group By A.account
/```
Above query is variation of beilstwh's query.
Problem with this query is that-
Say I have 3 rows: (ACCOUNT|AMOUNT)

A1001 \$600
A1001 \$-200
A1001 \$-100

The result for thsi query comes as: (Account|Amount|Amount|difference)

A1001 \$1200 \$-300 \$900

The \$1200 in orange is extra amount because \$600 gets repeated as many times the number of rows having negative amount (in this case 2).

I wrote the following query and problem now is that all negative values are summed up in one row and positive values in second row:

Code:
```select account,sum(posted_total_amt)"dr",0"cr" from ps_ledger where
posted_total_amt>0 and account = A1001 group by account union select account,0,sum(posted_total_amt)
from ps_ledger where posted_total_amt<0 and account = A1001 group by account
/```
Result for above query is: (Account|Amount|Amount)

A1001 \$0 \$-300
A1001 \$600 \$0

The result I need is: (Account|Amount|Amount
A1001 \$600 \$-300

~BS.
Last edited by bhavesh78; 01-10-05 at 17:01.

4. Registered User
Join Date
Dec 2004
Posts
78
Hi again,

The solution for above would be:
Code:
```select account,sum("dr"),sum("cr") from (select account,sum(posted_total_amt)"dr",0"cr" from ps_ledger where
posted_total_amt>0 and account = A1001 group by account union select account,0,sum(posted_total_amt)
from ps_ledger where posted_total_amt<0 and account = A1001 group by account) group by account
/```

5. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
A more simple solution would be:
Code:
```Select A.Account
,Sum(Case When A.Posted_Total_Amt > 0
Then A.Posted_Total_Amt
Else 0 End) Debit
,Sum(Case When A.Posted_Total_Amt < 0
Then A.Posted_Total_Amt
Else 0 End) Credit
,Sum(A.Posted_Total_Amt) Difference
From Ps_Ledger A
Where A.Account = A1001
And A.Posted_Total_Amt Is Not Null
Group By A.Account;```

Last edited by LKBrwn_DBA; 01-10-05 at 17:33.

6. Registered User
Join Date
Dec 2004
Posts
78
Query by LKBrwn_DBA also works great. But right now I am using the following because we are using an addin software to create queries and it does not allow CASE logic-

Code:
```select account,sum(dr) DEBIT,sum(cr)*(-1) CREDIT, sum(dr)-sum(cr) "DR-CR" from
(select account,posted_total_amt dr,0 cr,accounting_period from ps_ledger where
posted_total_amt>=0 and accounting_period=1 union select account,0,posted_total_amt,accounting_period
from ps_ledger where posted_total_amt<=0 and accounting_period=1)
group by account
/```
Thanks for all your time and effort.

~BS.
Last edited by bhavesh78; 01-10-05 at 18:46.

7. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
Carefull with this: sum(dr)-sum(cr), because cr is the sum of negative numbers!
better use: sum(dr+cr)

8. Registered User
Join Date
Dec 2004
Posts
78
Thanks. It was just to understand the query better.

~BS

9. Registered User
Join Date
Dec 2004
Posts
78
A more optimized way would be:

Code:
```select account,(select sum(posted_total_amt) from ps_ledger
where accounting_period=1 and account=a.account and posted_total_amt >= 0) cr,
(select sum(posted_total_amt)*(-1) from ps_ledger
where accounting_period=1 and account=a.account and posted_total_amt <= 0) dr
from ps_ledger a
where a.accounting_period=1
group by a.account
/```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•