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

Question

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

Valued Member

Points: 62

November 8, 2019 at 3:27 am

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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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: 63503 More actions · Answer 2

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