Reputation: 9074
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
Upvotes: 0
Views: 93
Reputation: 24086
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
Upvotes: 2
Reputation: 28695
Try query bellow:
Select a.AdmissionNum, a.StudName, a.Fees, b.RemainingFees
from Payment a
inner join paymentDetails b on a.AdmissionNum = b.AdmissionNum
inner join (
select AdmissionNum, max(date) as date
from paymentDetails
group by AdmissionNum
) c on a.AdmissionNum = c.AdmissionNum
where b.RemainingFees > 0 and b.Date = c.Date
Upvotes: 0
Reputation: 34367
Try below:
SELECT a.AdmissionNum, a.StudName , a.Fees, b.remFees as RemainingFees
FROM Payment a,
(SELECT AdmissionNum, min(RemainingFees) as remFees
FROM PaymentDetails b GROUP BY AdmissionNum) b
WHERE a.AdmissionNum = b.AdmissionNum
and b.remFees >0;
Upvotes: 0
Reputation: 24134
I would do it as follows.
SELECT
P.AdmissionNum,
P.StudName,
P.Fees
P.Fees - PD.Payment 'RemainingFees'
FROM
Payment P
JOIN (SELECT
AdmissionNum,
SUM(Payment) 'Payment'
FROM
PaymentDetails
GROUP BY AdmissionNum
) AS PD
ON P.AdmissionNum = PD.AdmissionNum
WHERE
P.Fees - PD.Payment > 0
Upvotes: 0