How to subtract two values in sql server which are in different table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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           |admission_fee  |200
1001           |tution_fee     |500
1001           |transportation |500

table2 fee_paid_details

VB

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 20:51pm

ntitish

Add a Solution

Comments

Sandeep Mewara 2-Mar-13 3:24am

Ok and the issue is? Where are you stuck?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

willington.d · Accepted Answer · 2013-03-01T21:40:00

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'
    AND A.Fee_type = 'admission_fee'

Posted 1-Mar-13 21:40pm

willington.d

Comments

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'

OriginalGriff · Accepted Answer · 2013-03-01T21:49:00

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

Posted 1-Mar-13 21:49pm

OriginalGriff

Comments

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'