Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Challenge Question

Hi Friends,
  I have three tables.

FeeDetails:

classcode
term1amt
term2amt
term3amt
term1duedate
term2duedate
term3duedate

Students:

studentno
name
concession

Receipts:

receiptno
studno
termno
amount
rtdate


I want a query which shows all the students who have not payed their amount till the term1duedate based on ther termno.

Some students may have concession. The Query should calculate amount for a student who have the concession and that should check with total amount payed by that student(receipts) till date in that term(1/2/3) is less than the actual amount to be payed in that term(from feedetails).

Pls give me the query.
Any suggession is greatly appreciated.
Thanking you.
0
mrajanikrishna
Asked:
mrajanikrishna
  • 2
  • 2
1 Solution
 
swh062500Commented:
    I don't see a clear connection between the Receipts table and the FeeDetails table.  FeeDetails.ClassCode doesn't seem to connect to either of the 2 tables.
     Also, howmany receipts per TermNo will one StudNo have?  1 or many?  I am wondering if per student, the receipts need to be summed, or if one record is enough.  
0
 
swh062500Commented:
    I think this is close to what you want.  I'm not sure about how FeeDetails.ClassCode is being used.  

     The code below is using a LEFT in case there are no receipts for a particular student.  You will need to

select s.studentNo, s.Name
from (students as S left join receipts as R
on s.studentNo = r.studno) , FeeDetails as FD

HAVING sum(amount) + concession <=  FD.Term1Amt
WHERE r.termno = 1
AND r.rtdate <  FD.Term1DueDate
AND FD.ClassCode = ???
GROUP BY s.studentNo, R.termo
0
 
mrajanikrishnaAuthor Commented:
Yes, I need the same query

Thanks
0
 
mrajanikrishnaAuthor Commented:
Hi SWH,

    I am not getting the O/P. Error is   Join not supported.

What is the problem.  


Give  me reply.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now