locked
I want Three tables to be joined and columns to be computed based on their values RRS feed

  • Question

  • User117660978 posted

    I have 3 tables

    TABLE A (This Holds opening Balance of every individual student)

     ===========================================
     |  Studid  | FeeHeadId | Amount |   AS_ON |
     ===========================================
     |    1     |     1     |   33   |2015-2016|
     |    1     |     2     |   11   |2015-2016|
     |    1     |     3     |    0   |2015-2016|
     |    1     |     4     |    0   |2015-2016|
     ===========================================

    N:B:- The above table does not have record for all students. Thus only the students who have any outstanding amounts till 31-03-2018 are stored here.

    TABLE B (This Holds Applicable Fees for current year of every individual student)

     =============================================
     |  Studid  | FeeHeadId | Amount |  Session  |
     =============================================
     |    1     |     1     |   11   | 2016-2017 |
     |    1     |     2     |   21   | 2016-2017 |
     |    1     |     3     |   31   | 2016-2017 |
     |    1     |     4     |   41   | 2016-2017 |
     =============================================

    N:B:- The above table holds record of total applicable course fees for each student.

    TABLE C (This Holds All Paid Fees details of every individual student till date)

     =============================================
     |  Studid  | FeeHeadId | Amount |   Date    |
     =============================================
     |    1     |     10    |   11   | 01/09/2016|
     |    1     |     11    |   11   | 01/11/2016|
     |    1     |      1    |   11   | 30/11/2016|
     |    1     |      2    |   11   | 01/12/2016|
     =============================================

    I want to show total outstanding amount till date of a particular student.

    N:B:- I want [table a].[amount] to be added with [table b].[amount] and then [table c].[amount] to be deducted from the result

    Output I need as (total opening bal. Vs outstanding vs paid):

     ===============================================================
     |  Studid  | FeeHeadId |OPENING|PAID| BALANCE_DUE |   Date    |
     ===============================================================
     |    1     |     10    |   22  | 11 |    11       |01/09/2016 |
     |    1     |     11    |   32  | 11 |    21       |01/11/2016 |
     |    1     |      1    |   42  | 11 |    31       |30/11/2016 |
     |    1     |      2    |   52  | 11 |    41       |01/12/2016 |
     ===============================================================

    N:B: In the above result set,

    opening balance = sum([table a].[amount]) (if any) + sum([table b].[amount]),

    Paid = sum([table b].[amount])

    Balance due = opening - paid

    Friday, November 8, 2019 3:12 AM

All replies

  • User288213138 posted

    Hi sriguru_kunu,

    According to your description, I couldn’t understand your requirement clearly.

    Output I need as (total opening bal. Vs outstanding vs paid):

     ===============================================================
     |  Studid  | FeeHeadId |OPENING|PAID| BALANCE_DUE |   Date    |
     ===============================================================
     |    1     |     10    |   22  | 11 |    11       |01/09/2016 |
     |    1     |     11    |   32  | 11 |    21       |01/11/2016 |
     |    1     |      1    |   42  | 11 |    31       |30/11/2016 |
     |    1     |      2    |   52  | 11 |    41       |01/12/2016 |
     ===============================================================

    N:B: In the above result set,

    Do you want to output the fourth table based on the data from theTable A B C?

    If so, I found that the calculation formula you gave does not match the data in your table.

    opening balance = sum([table a].[amount]) (if any) + sum([table b].[amount]),

    Such as opening balance = sum([table b].[amount]) (if any) + sum([table c].[amount]) instead of 'table a'+'table c';

    Paid = sum([table b].[amount])

    This is your table c amount.

    and what is the relationship between these three tables? What does the 'AS_ON', 'Session', 'Date' mean?

    Please post more details information about your requirement.

    Best regards,

    Sam 

    Friday, November 8, 2019 6:42 AM