SQLTeam.com | Weblogs | Forums

SQL Queries for few questions

Need help to answer following questions ?

  1. Total invoice amount
  2. Total receipt amount
  3. Total invoice amount not paid
  4. Total amount paid without invoices
  5. Please write an SQL script to show total amount paid by the employer for the following scenario:
    

Students enrolled in courses (see the course offering, and course connection objects – the course connection object acts as the enrollment table of a contact to a course) and received invoices. The students submitted the invoices to their employers. The employers paid the invoices and receipts were issued to them. Records of both student and employer as contacts.

Is this real world work related or homework/test?

This is for a test.

Yep... so was your other question. I appreciate your honesty but, lordy, at least show you've tried.

I figured out the first three questions. Can you please help me to write the script for the following question.

Total amount paid by the employer for the following scenario:
•Students enrolled in courses (see the course offering, and course connection objects – the course connection object acts as the enrollment table of a contact to a course) and received invoices. The students submitted the invoices to their employers. The employers paid the invoices and receipts were issued to them. Records of both student and employer as contacts.

Looking at the Receipt table above, there's got to be a Contact table somewhere to satisfy the requirement for the sf_contact_id. Seems to me there should be at least a hint of whether the contact is a student or not in the missing Contact table.

SF_CONTACT_ID refers to a contact object not pictured.

Heh... I get that... it's seriously obvious that it's missing. What you're missing is that we need to know more about that table in order to help you.

Ok.Don't worry about it. Thank you very much for your time.