Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
i am facing problem in joining query for the tables
 
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'
 
//Student_Fee_Quotations table
student_id|fee_type   |amount|
1001      |tution_fee |1000  |
 

//FeePaid_Details
student_id|fee_type   |month|paid_amount
1001      |tution_fee |jan  |500
1001      |tution_fee |Feb  |300
 
the above code for getting the below table
student_id|fee_type  |amount|previous_monthdue|current_monthdue|total_due
1001      |tution_fee|1000  |500              |700             |1200
 
it is coming if there is any records in the table.but for the first time there will not be any records in the table for payment...that was my problem.
if there is no record means for the current_monthdue should be equal to amount and if there is no records of previous_monthdue means previous_monthdue should be zero.like below table
student_id|fee_type  |amount|previous_monthdue|current_monthdue|total_due
1001      |tution_fee|1000  |  0              |1000            |1000
Posted 7-Mar-13 2:10am
ntitish1.5K
Edited 7-Mar-13 3:15am
v6
Comments
Davidduraisamy at 7-Mar-13 7:15am
   
What issue you are facing?
ntitish at 7-Mar-13 7:18am
   
above at last i updated my problem..
Davidduraisamy at 7-Mar-13 7:24am
   
Try this:
select q.Student_ID,q.Fee_Type,q.amount,case when q.amount IS null then q.amount else q.amount-p.paid end as Current_month_due,case when g.paid is null then 0 else q.amount-g.paid end 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 7:48am
   
sir i think so you are not getting my point. from above in second table(FeePaid_Details) if there is no records then my table should be like 4th table (at last i updated na) sir that was my problem i am joining p,g to q if there is no records in p or if there is no records in g or there is no records in p,g the table should be like fourth table...

1 solution

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

Solution 1

use left outer join for this
 
and take value isnull(current_monthdue,0) as current_monthdue
  Permalink  
Comments
ntitish at 7-Mar-13 7:33am
   
above is my code can u modify that if you dont mind means ...dont think badly sir i am confused from morning with this query i am not getting the exact point...if u can only sir other wise thanks for your valuable help....
itsmehaboob at 7-Mar-13 7:42am
   
Please try this.
 
SELECT q.student_id, q.fee_type, q.amount, q.amount - p.paid AS Current_month_due, ISNULL(q.amount, 0) - ISNULL(g.paid, 0) AS previous_month_due, (q.amount - g.paid)
+ (q.amount - p.paid) AS total_amount
FROM Student_Fee_Quotations AS q LEFT OUTER JOIN
(SELECT student_id, fee_type, SUM(paid_amount) AS paid
FROM FeePaid_Details AS d
WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'jan')
GROUP BY student_id, fee_type) AS g ON q.student_id = '1' AND q.fee_type = 'Admission Fee' LEFT OUTER JOIN
(SELECT student_id, fee_type, SUM(paid_amount) AS paid
FROM FeePaid_Details AS d
WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'feb')
GROUP BY student_id, fee_type) AS p ON q.student_id = '1' AND q.fee_type = 'Admission Fee'
ntitish at 7-Mar-13 8:08am
   
sir my problem you are not getting it was my fault only sir. i am unable to explain u clearly...sir again i will explain you if you are interested.sir we are joining tables na sir down i am explaining clearly
 
q AS Student_Fee_Quotations
 
g AS(SELECT student_id, fee_type, SUM(paid_amount) AS paid FROM FeePaid_Details AS d WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'jan') GROUP BY student_id, fee_type)
 
p AS (SELECT student_id, fee_type, SUM(paid_amount) AS paid FROM FeePaid_Details AS d WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'feb') GROUP BY student_id, fee_type)
 
like the above we are taking na sir if there is no records in g or if there is no records in p or if there is no records in p or g then the table should display like forth table in my question.sir i think u got my point.but with ur query it is not getting...is there any solution for this sir..

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

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web03 | 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