Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Actually i am facing problem in joining of two tables and getting the right information for example i am having two tables(Fee_Quotation,Paid_Details)
 
table 1
//Fee_Quotation table(while admission student will commit for this amount.here below there is one student fee_quotation details 1001)
 
Student_ID|Fee_Type         |Fee_Amount|Payment_Type   |Commited_on
1001      |Registration_fee |100       |Annualy        |2013-mar-8
1001      |Tution_fee       |1000      |Monthly        |2013-mar-8
1001      |Books            |1000      |Annualy        |2013-mar-8
 

table 2
//Paid_details table(here student will pay fee according to month)
 
Student_ID|Fee_Type         |Fee_Amount|Payment_Type |Month  |Paid_on
1001      |Registration_fee |100       |Annualy      |mar    |2013-mar-11
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-13
1001      |Books            |500       |Annualy      |mar    |2013-mar-11
1001      |Books            |300       |Annualy      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |apr    |2013-apr-12
1001      |Tution_fee       |400       |Monthly      |apr    |2013-apr-13
 

now i will pass three parameters like Student_ID=@Student_ID,Payment_type=@Payment_Type,Month=@Month
ex:- Student_ID=1001,Payment_Type='Monthly',Month='apr'
then i want the table like below
 

Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Tution_Fee |1000      |0.00     |100         |100
 
if i will pass two parameters like
Student_ID=@Student_ID,Payment_type=@Payment_Type
ex:- Student_ID=1001,Payment_Type='Annualy'
then i want the table like below
Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Regist_Fee |100       |0.00     |100         |100
1001      |Books      |1000      |0.00     |1000        |1000

 

//this type of table we can get ha sir....i mean it is possible ha sir
 

[MOVED] from comment [/MOVED]
 select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount 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 on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' 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='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
Posted 6-Mar-13 22:08pm
ntitish1.5K
Edited 7-Mar-13 4:39am
v5
Comments
Ankur\m/ at 7-Mar-13 3:23am
   
Your db design doesn't look good to me. 1) There is no primary key. 2) There is a lot of redundancy (tables aren't normalized).
Anyways, can you show us what query you tried and we will be able to help with what was wrong.
ntitish at 7-Mar-13 3:42am
   
even i said to my Tl he is saying nothing will happen in our project we will not deal with more records...if we came to question i written query like below here i am getting Student_id,fee_type,Fee_Amount,Current_Month_Due
 
select q.Student_ID,q.Fee_Type,q.amount,q.amount-g.paid as Current_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='Registration Fee' and d.Month_Details='jan'
group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Registration Fee'
ntitish at 7-Mar-13 6:24am
   
//this was my current code i am getting output but here again i am getting onother problem...that was i am getting out put when there is a record in table if there is no record in the table that means he is paying first time of his fee....for jan then there will not be any records na sir..then what should i do..
 
select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount 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 on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' 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='feb'
group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
Maciej Los at 7-Mar-13 6:33am
   
Improve you question, use: "Improve Question" widget.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can achieve that with stored procedure[^] like this:
CREATE PROCEDURE GetStudentFees()
    @Student_ID INT,
    @Payment_Type NVARCHAR(30),
    @Month NVARCHAR(30)
AS
BEGIN
 
    IF (ISNULL(@Month,'')='')
        BEGIN
            --your code to get data for 2 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type
            GROUP BY [Student_ID], [Fee_Type]
        END
    ELSE
        BEGIN
            --your code to get data for 3 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type AND [Month] = @Month
            GROUP BY [Student_ID], [Fee_Type]
        END
END
 
I don't know what you mean LastMoDue And CurrentMoDue, so... i can't help you.
  Permalink  
v2
Comments
ntitish at 7-Mar-13 4:37am
   
sir with my query i am getting only student_id,fee_type,fee_amount,current_monthdue
but i want to get last_monthdue,total_due.after getting that i can use ur syntax ...but how to get that two details
ntitish at 7-Mar-13 5:16am
   
this was my code sir, you can see once again which i modified after that then also it is not working sir
 

select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount 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 on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' 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='feb'
group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
ntitish at 7-Mar-13 5:38am
   
sir i am waiting for ur answer...
Maciej Los at 7-Mar-13 6:33am
   
See my updated solution.
ntitish at 7-Mar-13 7:30am
   
sir when i am paying any student fee means first i will select annualy or monthly from drop_down1 if i select annualy means according to annualy the fesses will come in a grid view if when i select monthly means another drop_down2 will display with 12 months from that we will select month according to that month monthly fesses will display of the particular student in that gridviewfee details of monthly and month of feb means i will display student_id,fee_type,amount(what they will commit while admission),previous_month_due(under this the due of the jan if any due is there means),current_month_due(under this the due of the feb if any due is there means).i think so u can understand now....if not it was my fault sir.
Maciej Los at 7-Mar-13 12:16pm
   
Sorry, but i don't understand... ;(
You need to change sql query text depending on your needs.

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

  Print Answers RSS
0 Kornfeld Eliyahu Peter 169
1 Zoltán Zörgő 139
2 George Jonsson 135
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 7 Mar 2013
Copyright © CodeProject, 1999-2014
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