I want Three tables to be joined and columns to be computed based on their value

  • sriguru_kunu

    SSC Rookie

    Points: 32

    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
    .[amount] to be added with
    .[amount] and then
    .[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(
    .[amount]) (if any) + sum(
    .[amount]),

    Paid = sum(
    .[amount])

    Balance due = opening - paid
  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • pietlinden

    SSC Guru

    Points: 62607

    Your tables are pretty, but that's not a helpful way to post sample data. Useful is stuff we can copy & paste into SSMS and run it. Please read this and follow the instructions outlined in it. Then you stand a much better chance of getting a tested and correct answer.

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply