Link to home
Start Free TrialLog in
Avatar of sam2929
sam2929

asked on

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

This result:
| acct_no |  debit | credit |
|---------|--------|--------|
|     123 |  -2880 |      0 |
|     123 | -53572 |      0 |
|     456 |      0 |   2880 |
|     456 |      0 |  53572 |
|     456 | -82371 |      0 |
|     789 |      0 |  82371 |

Open in new window

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
;

Open in new window


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

Open in new window


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
;

Open in new window


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

Open in new window

Avatar of sam2929
sam2929

ASKER

Thanks but i need balance
So would I like balance. I simply provided what you asked for in the question.

You just add the 2 columns don't you?
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sam2929, do you still need help with this question?