i am facing problem in joining query for the tables
select
q.Student_ID
,q.Fee_Type
,q.amount
,q.amount-p.paid as Current_month_due
,q.amount-g.paid as previous_month_due
,(q.amount-g.paid)+(q.amount-p.paid) as total_amount
from
Student_Fee_Quotations q
join
(
select
d.Student_ID
,d.Fee_Type
,sum(d.amount) as paid
from
FeePaid_Details d
where
d.Student_ID='PS20130001'
and d.Fee_Type='Admission Fee'
and d.Month_Details='jan'
group by
d.Student_ID
,d.Fee_Type
) as g
on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
join
(
select
d.Student_ID
,d.Fee_Type
,sum(d.amount) as paid
from FeePaid_Details d
where
d.Student_ID='PS20130001'
and d.Fee_Type='Admission Fee'
and d.Month_Details='feb'
group by
d.Student_ID
,d.Fee_Type
) as p
on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
//Student_Fee_Quotations table
student_id|fee_type |amount|
1001 |tution_fee |1000 |
//FeePaid_Details
student_id|fee_type |month|paid_amount
1001 |tution_fee |jan |500
1001 |tution_fee |Feb |300
the above code for getting the below table
student_id|fee_type |amount|previous_monthdue|current_monthdue|total_due
1001 |tution_fee|1000 |500 |700 |1200
it is coming if there is any records in the table.but for the first time there will not be any records in the table for payment...that was my problem.
if there is no record means for the current_monthdue should be equal to amount and if there is no records of previous_monthdue means previous_monthdue should be zero.like below table
student_id|fee_type |amount|previous_monthdue|current_monthdue|total_due
1001 |tution_fee|1000 | 0 |1000 |1000