How to join and get result in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have two sql table Fee_Payable_to_Students and Fee_Receipt_Students

Fee_Payable_to_Students has value as

S_Adm_No | Student | FheadId     |FHead            |    Apr | May | Jun   |
 1001    | Ramesh  |  1          | Admission Fee   |  25000 |   0 | 25000 |
 1001    | Ramesh  |  2          | Annual Fee      |   6600 |   0 |  6600 |
 1001    | Ramesh  |  3          | Tuition Fee     |   1900 |1900 |  1900 |

Fee_Receipt_Students

S_Adm_No | Student | FheadId     |FHead            |    Apr | May | Jun   |
 1001    | Ramesh  |  1          | Admission Fee   |  25000 |   0 | 25000 |
 1001    | Ramesh  |  2          | Annual Fee      |   6600 |   0 |  6600 |

Now I want to display as

S_Adm_No| Student | FheadId |FHead          |    Apr | May | Jun   | Q1  |R_Amt|Bal  |
 1001   | Ramesh  |  1      | Admission Fee |  25000 |   0 | 25000 |50000|63200|5700 |
 1001   | Ramesh  |  2      | Annual Fee    |   6600 |   0 |  6600 |13200|63200|5700 |
 1001   | Ramesh  |  3      | Tuition Fee   |   1900 |1900 |  1900 | 5700|63200|5700 |

Note: R_Amt comes from Fee_Receipt_Students as alias

my code is below. Some please help...

[edit]Code block added - OriginalGriff[/edit]

What I have tried:

SQL

select	
		P.S_Adm_No,P.S_Name	,P.fheadid,P.FHead,P.apr,P.may,P.Jun, 
		(P.apr+P.may+P.Jun)Q1, 
		SUM(R.Apr+R.May+R.Jun) R_Amt 
FROM	
		Fee_Payable_to_Students AS P LEFT JOIN Fee_Receipt_Students AS R 
		ON P.S_Adm_no = R.S_Adm_no AND P.cls_SecId = R.cls_SecId 
where	P.S_Adm_No='1001' 
group by 
		P.S_Name,P.fheadid,P.S_Adm_No,P.apr,P.may,P.Jun,P.FHead,R.S_Adm_No    
order by P.S_Adm_No

Posted 2-Dec-16 19:42pm

Member 12245539

Updated 3-Dec-16 4:05am

OriginalGriff

v4

Add a Solution

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)

OriginalGriff · Answer 1 · 2016-12-02T23:16:00

Personally? I'd dump the lot and redesign the database.
That's not an efficient design - it duplicates info, restricts you to three specific months, and generally makes your life difficult.
Instead, set up a Students table, which contains the StudentID (which I assume is the S_Adm_no), name and any other relevant info (address, email, mobile, other contact details, etc.)
Then add a FeeTypes table, containing the FeeID (FheadId I assume) and Description (FHead).
Then set up a Transactions table, containing a TransactionID, Date, StudentID, FeeID, and Value
Now when a fee becomes due, it's added to the Transactions with a positive Value.
When a student payment arrives, it's added to the Transactions with a negative Value.
It's then a relatively simple matter to extract the info you need without the ridiculous number of GROUP BY clauses you are adding.

Peter Leow · Answer 2 · 2016-12-03T04:05:00

Solution 2

OriginalGriff is right, dump that database of yours. Cos' they are not database tables at all, more like excel sheets. But database tables are never excel sheets. You will encountered a lot of problems trying to manipulate these kind of tables. In fact, you already did. So start learning the correct way of design a relational database => Introduction to database design[^] and 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]

Posted 3-Dec-16 4:05am

Peter Leow

Updated 3-Dec-16 4:06am

v2

Comments

Member 12245539 4-Dec-16 23:04pm

Fee_Receipt_Students is view only not a table.....

Peter Leow 4-Dec-16 23:14pm

Whatever, visit the links and learn the correct way of database design.

Member 12245539 4-Dec-16 23:22pm

ok
thanks