Click here to Skip to main content
11,410,750 members (65,170 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
i am having two tables with fee details (fee_details_memo,fee_paid_details)

table1 fee_details_memo
registration_id|Fee_type       |Amount
1001           |admission_fee  |200
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.6K
Comments
Sandeep Mewara at 2-Mar-13 3:24am
   
Ok and the issue is? Where are you stuck?
Rate this: bad
good
Please Sign up or sign in to vote.

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'
  Permalink  
Comments
ntitish at 2-Mar-13 5:11am
   
thanks sir..........
ntitish at 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: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
ntitish at 2-Mar-13 5:11am
   
thanks sir..........
OriginalGriff at 2-Mar-13 5:16am
   
You're welcome!
ntitish at 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 at 2-Mar-13 6:37am
   
Why? It looks like you already have the solution... use a LIKE in the WHERE clause.
ntitish at 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 at 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 at 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 at 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 at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,920
1 OriginalGriff 6,918
2 Maciej Los 3,390
3 Abhinav S 3,248
4 Peter Leow 3,059


Advertise | Privacy | Mobile
Web04 | 2.8.150414.5 | Last Updated 2 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100