15,960,578 members
See more:
i am having two tables with fee details (fee_details_memo,fee_paid_details)

table1 fee_details_memo
VB
```registration_id|Fee_type       |Amount
1001           |tution_fee     |500
1001           |transportation |500```

table2 fee_paid_details
VB
```registration_id|Fee_type       |Amount |Paid_On

now what i want is from table1 the "admission_fee" is 200 and from table2 the sum of the "admission_fee" is 160 then i want to subtract the value of first table with the value of second table.and the out put value i need, is it possible
result-> 200-160=40
Posted
Sandeep Mewara 2-Mar-13 3:24am
Ok and the issue is? Where are you stuck?

## Solution 1

SQL
```SELECT
A.REGISTRATION_ID, A.Fee_type, A.AMOUNT, B.AMOUNT AS PAID, (A.AMOUNT - B.AMOUNT) AS DUE
FROM
TABLE1 AS A
CROSS APPLY (SELECT SUM(AMOUNT) AS AMOUNT FROM TABLE2 WHERE REGISTRATION_ID = '1001' AND Fee_type = 'admission_fee' GROUP BY Fee_type) AS B
WHERE
A.REGISTRATION_ID = '1001'

ntitish 2-Mar-13 5:11am
thanks sir..........
ntitish 7-Mar-13 5:22am
//is this query is correct ha sir...

select q.Student_ID,q.Fee_Type,q.amount,q.amount-g.paid as Current_month_due ,q.amount-p.paid as previous_month_due 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 (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'

## Solution 2

SQL
```SELECT m.registration_id, m.Fee_type, m.Amount - g.Paid as Owing FROM fee_details_memo m
JOIN (SELECT p.registration_id, p.Fee_type, SUM(p.Amount) as Paid FROM fee_paid_details p
GROUP BY p.registration_id, p.Fee_type) AS g
ON m.registration_id= g.registration_id AND m.Fee_type = g.Fee_type```

ntitish 2-Mar-13 5:11am
thanks sir..........
OriginalGriff 2-Mar-13 5:16am
You're welcome!
ntitish 2-Mar-13 6:24am
sir if u dont mind can u see this link can u solve my problem
http://www.codeproject.com/Questions/554180/Howplustoplussearchplusnameplusinplusaplusparticul
OriginalGriff 2-Mar-13 6:37am
Why? It looks like you already have the solution... use a LIKE in the WHERE clause.
ntitish 2-Mar-13 6:58am
sir i think so you are not getting my problem...it is my fault only.. ok i will explain u again. i am having 5 students with names suresh,siddu,sai,snehal,mahesh if i passed the @Student_ID=s means the first four names should be displayed...but my query is not giving that result .it is giving the result when ever i given a correct name like pavan means it is show ing the record of pavan... below is my sqlquery which i writen in sql db. select * from student_details where (child_name like @Student_ID or student_id like @Child_Name) and School_Name=@School_Name