# credit-debit calculations

Hi,
I ahve two tables Account and Transfer there is one to many relationship from account to transfer

Account

acct_id acct_no
28          123
09          456
33         789

Transfer look like below:

Transfer_qt   transfer_type_cd    source_acct_id   target_acct_id
2880                  2                                28                     09
53572              11                                28                    09
82371               12                                09                  33

Join Account with Transfer as below

select a.* from account a
left outer join transfer b
on a.acct_id =b.source_acct_id or   a.acct_id =b.target_acct_id

i want to add in Account Account_balance_qty

Account

acct_id acct_no  Account_balance_qt
28          123          -2800-53572
09          456        2800+53572-82371(target -source qty)
33         789          82371

Basic idea is credit-debit Transfer_qty how can i do it
PortletPaul

This result:
``````| acct_no |  debit | credit |
|---------|--------|--------|
|     123 |  -2880 |      0 |
|     123 | -53572 |      0 |
|     456 |      0 |   2880 |
|     456 |      0 |  53572 |
|     456 | -82371 |      0 |
|     789 |      0 |  82371 |``````
Produced by this query:
``````SELECT
a.acct_no
, ((case when a.acct_id = dc.source_acct_id then dc.Transfer_qt else 0 end) * - 1) as debit
, ( case when a.acct_id = dc.target_acct_id then dc.Transfer_qt else 0 end)        as credit
FROM account a
LEFT OUTER JOIN transfer dc
ON a.acct_id = dc.source_acct_id
OR a.acct_id = dc.target_acct_id
;``````

or, summarised by account:
``````| acct_no |  debit | credit |
|---------|--------|--------|
|     123 | -56452 |      0 |
|     456 | -82371 |  56452 |
|     789 |      0 |  82371 |``````

by this query using SUM() and GROUP BY:
``````SELECT
a.acct_no
, SUM((case when a.acct_id = dc.source_acct_id then dc.Transfer_qt else 0 end) * - 1) as debit
, SUM( case when a.acct_id = dc.target_acct_id then dc.Transfer_qt else 0 end)        as credit
FROM account a
LEFT OUTER JOIN transfer dc
ON a.acct_id = dc.source_acct_id
OR a.acct_id = dc.target_acct_id
GROUP BY
a.acct_no
;``````

Note the use of case expressions inside the sum
SUM( CASE .... END )
which enables the distinction between credit and debit

details:
``````   CREATE TABLE Account
([acct_id] int, [acct_no] int)
;

INSERT INTO Account
([acct_id], [acct_no])
VALUES
(28, 123),
(09, 456),
(33, 789)
;

CREATE TABLE Transfer
([Transfer_qt] int, [transfer_type_cd] int, [source_acct_id] int, [target_acct_id] int)
;

INSERT INTO Transfer
([Transfer_qt], [transfer_type_cd], [source_acct_id], [target_acct_id])
VALUES
(2880, 2, 28, 09),
(53572, 11, 28, 09),
(82371, 12, 09, 33)
;

**Query 1**:

SELECT
a.acct_no
, ((case when a.acct_id = dc.source_acct_id then dc.Transfer_qt else 0 end) * - 1) as debit
, ( case when a.acct_id = dc.target_acct_id then dc.Transfer_qt else 0 end)        as credit
FROM account a
LEFT OUTER JOIN transfer dc
ON a.acct_id = dc.source_acct_id
OR a.acct_id = dc.target_acct_id

**[Results][2]**:
| acct_no |  debit | credit |
|---------|--------|--------|
|     123 |  -2880 |      0 |
|     123 | -53572 |      0 |
|     456 |      0 |   2880 |
|     456 |      0 |  53572 |
|     456 | -82371 |      0 |
|     789 |      0 |  82371 |
**Query 2**:

SELECT
a.acct_no
, SUM((case when a.acct_id = dc.source_acct_id then dc.Transfer_qt else 0 end) * - 1) as debit
, SUM( case when a.acct_id = dc.target_acct_id then dc.Transfer_qt else 0 end)        as credit
FROM account a
LEFT OUTER JOIN transfer dc
ON a.acct_id = dc.source_acct_id
OR a.acct_id = dc.target_acct_id
GROUP BY
a.acct_no

**[Results][3]**:
| acct_no |  debit | credit |
|---------|--------|--------|
|     123 | -56452 |      0 |
|     456 | -82371 |  56452 |
|     789 |      0 |  82371 |

[1]: http://sqlfiddle.com/#!3/3c84d/6``````
sam2929