I have two tables.
Table1->Payment
AdmissionNum(PK) | StudName | Fees |
1 sagar 5000
2 nilesh 6000
3 amar 4000
Table2-> paymentDetails
AdmissionNum(FK) | RemainingFees | Date | Payment
1 4000 1/1/2012 1000
2 5000 2/5/2012 1000
1 3500 2/10/2012 1500
1 2500 2/15/2012 1000
2 2500 2/20/2012 2500
3 0 3/25/2012 4000
I have tried different queries but was not able to make joint between payment table and paymentDetails table. I am making C# windows app. When particular student is paying fees his record is stored in paymentDetails table. I want to extract only outstanding fees details i.e. student whose Remaining Fees is greater than 0 but the last details in the paymentDetais table i.e. .
My expected resut table is-
AdmissionNum | StudName | Fees | RemainingFees
1 sagar 5000 2500
2 nilesh 6000 2500
try this:
with cte as (select P.AdmissionNum , P.StudName, P.Fees , D.RemainingFees,row_number()
over ( partition by P.AdmissionNum order by [DATE] desc) as rn
from Payment P
join PaymentDetails D
on P.AdmissionNum=D.AdmissionNum
where RemainingFees>0)
select AdmissionNum ,StudName, Fees , RemainingFees
from cte
where rn=1