Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can I display like


studentId Apr              May           Jun          <br />
      xyz        sum of apr        sum of may    sum of Jun<br />
<br />
like wise for all month<br />
           <br />
<br />


There are two table available FeeAssigned and FeeExemption.


SQL
CREATE TABLE FeeAssigned ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
 
CREATE TABLE FeeExemption ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
 
--INSERT DATA
INSERT INTO FeeAssigned 
SELECT 'TUITION FEE',120,120,120,120,120,120,120,120,120,120,120,120,'STUD01'
UNION ALL
SELECT 'TRAN FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
 
INSERT INTO FeeExemption
SELECT 'TUITION FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
UNION ALL
SELECT 'TRAN FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'


What I have tried:

<br />
CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))<br />
AS<br />
BEGIN<br />
SELECT FA.StudentId,FA.[FEE HEAD],<br />
FA.APR - ISNULL(FET.APR,0) AS APR,<br />
FA.MAY - ISNULL(FET.MAY,0) AS MAY,<br />
FA.JUN - ISNULL(FET.JUN,0) AS JUN,<br />
FA.JUL - ISNULL(FET.JUL,0) AS JUL,<br />
FA.AUG - ISNULL(FET.AUG,0) AS AUG,<br />
FA.SEP - ISNULL(FET.SEP,0) AS SEP,<br />
FA.OCT - ISNULL(FET.OCT,0) AS OCT,<br />
FA.NOV - ISNULL(FET.NOV,0) AS NOV,<br />
FA.DEC - ISNULL(FET.DEC,0) AS DEC,<br />
FA.JAN - ISNULL(FET.JAN,0) AS JAN,<br />
FA.FEB - ISNULL(FET.FEB,0) AS FEB,<br />
FA.MAR - ISNULL(FET.MAR,0) AS MAR<br />
FROM FeeAssigned FA LEFT JOIN  <br />
							 (SELECT FE.StudentId,FE.[FEE HEAD],<br />
									 SUM(FE.APR) AS APR,<br />
									 SUM(FE.MAY) AS MAY,<br />
									 SUM(FE.JUN) AS JUN,<br />
									 SUM(FE.JUL) AS JUL,<br />
									 SUM(FE.AUG) AS AUG,<br />
									 SUM(FE.SEP) AS SEP,<br />
									 SUM(FE.OCT) AS OCT,<br />
									 SUM(FE.NOV) AS NOV,<br />
									 SUM(FE.DEC) AS DEC,<br />
									 SUM(FE.JAN) AS JAN,<br />
									 SUM(FE.FEB) AS FEB,<br />
									 SUM(FE.MAR) AS MAR <br />
							   FROM FeeExemption FE <br />
							GROUP BY  FE.StudentId,FE.[FEE HEAD]) AS FET ON (FA.StudentId=FET.StudentId) AND (FA.[FEE HEAD]=FET.[FEE HEAD])<br />
WHERE FA.StudentId=@StudentId<br />
END <br />
<br />
Posted
Updated 18-Aug-16 17:38pm
v3
Comments
RossMW 18-Aug-16 23:37pm    
It looks like you want to return a table or result set, so have you looked at a view or Table function rather than a stored procedure?

1 solution

Try this query .I am not handle null please handle null when you will come to know that it actually works for you.Happy coding :)
SQL
select FA.StudentId,SUM(FA.APR-FE.APR) As APR,SUM(FA.MAY-FE.MAY) As MAY,SUM(FA.JUN-FE.JUN)As JUN,SUM(FA.JUL-FE.JUL) As JUL
,SUM(FA.AUG-FE.AUG) As AUG,SUM(FA.SEP-FE.SEP) As SEP,SUM(FA.OCT-FE.OCT) As OCT,SUM(FA.NOV-FE.NOV) As NOV,SUM(FA.DEC-FE.DEC) As DEC
,SUM(FA.JAN-FE.JAN) As JAN,SUM(FA.FEB-FE.FEB) As FEB,SUM(FA.MAR-FE.MAR) As MAR from FeeAssigned FA

inner join FeeExemption FE on FA.StudentId=FE.StudentId AND FA.[FEE HEAD]=FE.[FEE HEAD]

group by FA.StudentId

And your result will be

StudentId	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
STUD01	150	150	150	150	150	150	150	150	150	150	150	150
 
Share this answer
 
Comments
Member 12245539 19-Aug-16 1:12am    
Boss if I increase more rows in FeeAssigned then result is remains same.

StudentId APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR
STUD01 150 150 150 150 150 150 150 150 150 150 150 150
Member 12245539 19-Aug-16 1:14am    
Rows will be increased as per requirement. like
Transport Fee
Annual Fee
Admission Fee
and so on so ..........
Anisuzzaman Sumon 19-Aug-16 1:38am    
will remain same :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900