I have Three tables, which need to be joined and columns to be computed based on their values

Transact-SQL
, , ,
1

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

2

hi it is not clear by looking what you want !!

please explain with data ...

example
TableA 1, 11
TableA 1, 11

sum for 1 =11+11 =21
3

i have created sample data script

please click arrow ot the left for "drop create sample data" 
drop table tableA
go 

create table tableA
(
Stuid int ,
FeeHeadId int ,
Amount int,
AS_ON varchar(100) 
)
go 

insert into tableA select 1, 1,33, '2015-2016'
insert into tableA select 1, 2,11, '2015-2016'
insert into tableA select 1, 3, 0, '2015-2016'
insert into tableA select 1, 4, 0, '2015-2016'
go 

select * from tableA
go 

drop table tableB
go 

create table tableB
(
Stuid int ,
FeeHeadId int ,
Amount int,
Session varchar(100) 
)
go 

insert into tableB select 1,1,11,'2016-2017'
insert into tableB select 1,2,21,'2016-2017'
insert into tableB select 1,3,31,'2016-2017'
insert into tableB select 1,4,41,'2016-2017'
go

select * from tableB
go 

drop table tableC
go 

create table tableC
(
Stuid int ,
FeeHeadId int ,
Amount int,
[Date] date
)
go 

insert into tableC select 1,10,11,'2016-09-01'
insert into tableC select 1,11,11,'2016-11-01'
insert into tableC select 1,1,11,'2016-11-30'
insert into tableC select 1,2,11,'2016-12-01'
go 

select * from tableC 
go

image
image594×755 120 KB