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
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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

0
sam2929Author Commented:
Thanks but i need balance
0
PortletPaulfreelancerCommented:
So would I like balance. I simply provided what you asked for in the question.

You just add the 2 columns don't you?
0
PortletPaulfreelancerCommented:
This result:
| acct_id | acct_no |  debit | credit | balance |
|---------|---------|--------|--------|---------|
|      28 |     123 | -56452 |      0 |  -56452 |
|       9 |     456 | -82371 |  56452 |  -25919 |
|      33 |     789 |      0 |  82371 |   82371 |

Open in new window


Produced by this query:
SELECT
        a.acct_id
      , 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
      , SUM((case when a.acct_id = dc.source_acct_id then dc.Transfer_qt else 0 end) * - 1)
      + SUM( case when a.acct_id = dc.target_acct_id then dc.Transfer_qt else 0 end)        as balance
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_id
      , a.acct_no
;

Open in new window


See it as a working demonstration at: http://sqlfiddle.com/#!3/3c84d/8

Note the individual [Credit] and [Debit] columns are included to demonstrate how the query operates and in this query you can now see how to add the two SUM()s to create [Balance]

ps: I had thought you wanted to see the individual credits & debits
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
sam2929, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.