13,345,438 members (49,271 online)
Rate this:
See more:
i am having two tables with fee details (fee_details_memo,fee_paid_details)

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

table2 fee_paid_details
```registration_id|Fee_type       |Amount |Paid_On
1001           |admission_fee  |100    |jan/2013
1001           |admission_fee  |50     |feb/2013
1001           |admission_fee  |10     |feb/2013```

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 1-Mar-13 21:51pm
ntitish1.9K
Sandeep Mewara 2-Mar-13 3:24am

Ok and the issue is? Where are you stuck?

Rate this:

## Solution 1

```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'
AND A.Fee_type = 'admission_fee'```
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'
Rate this:

## Solution 2

```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
OriginalGriff 2-Mar-13 7:13am

That's because your query needs to be something like:
SELECT * FROM myTable WHERE child_name LIKE @Student_ID + '%'
The percent character is a wildcard in SQL which means "match anything" much as "*" does in windows file names. Without at least one wildcard, LIKE is equivalent to equals.
ntitish 7-Mar-13 3:19am

sir i am getting one more problem in sql server 2005 here below i am giving the question link in code project sir
http://www.codeproject.com/Questions/557995/FacingplusProblemplusinplusJoiningplustwoplustable
ntitish 2-Mar-13 7:41am

thanks a lot sir...i solved my problem partially...
sir i am having one more problem. that was, when i am typing the name in the text-box like 's' i want to see the related records in grid-view.actually i am getting the result but when ever the cursor moving from the text_box it showing the grid-view with related records but what i want is when i am typing the name only i want to see the records is it possible sir....even i seen in so many shopping sites....
ntitish 7-Mar-13 5:24am

//Is this query is correct 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'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 215 Pete O'Hanlon 100 ProgramFOX 95 Richard Deeming 85 ppolymorphe 74
 OriginalGriff 3,038 Maciej Los 1,110 Karthik Bangalore 1,027 Jochen Arndt 865 ProgramFOX 828

Advertise | Privacy |
Web01 | 2.8.180111.1 | Last Updated 2 Mar 2013