I apologize in advance for being overly verbose. Hopefully someone can point me in the right direction.

I have a MSSQL 2005 database which tracks bills and payments for employees and their cell phones. The relationship between employees and their cell phones is many-to-many (employees can have more than one phone at a time and phone numbers can, over their lifespan, belong to more than one employee), and I have a table that maps those relationships. The bills and payments are tracked by relationship. Here's the schema of the database: http://i220.photobucket.com/albums/dd204/company_bucket_1234/company_bucket_help1.jpg

I have written a front end for the database and it works fine for users entering in information. I am currently working on reports for the application. I am using SQL Server 2005 Reporting Services to create the reports. Each report gets its data sets populated by calling a stored procedure. I am trying to create a stored procedure for a "totals" report that will display the following information in each row:

Employee Name, Phone Number, Total Amount Owed by this Employee for this Phone Number, Total Amount Paid by this Employee for this Phone Number

I can get this working for either the bills or the payments... but I cannot get both sets of data into a single row without the totals screwing up badly. Here's an example of my SELECT statements:

-- Sum of AmountOwed
SELECT     Employee.LastName, Employee.FirstName, Employee.MiddleName, Employee.Suffix,
                  CountyCellPhone.CellPhoneNumber, SUM(AmountOwed) AS TotalOwed
FROM         BillingHistory
                   INNER JOIN EmployeeCellPhoneRelationship ON EmployeeCellPhoneRelationship.PK_EmployeeCellPhoneRelationship = BillingHistory.FK_EmployeeCountyCellPhoneRelationship
               INNER JOIN Employee ON EmployeeCellPhoneRelationship.FK_Employee = Employee.PK_Employee
               INNER JOIN CountyCellPhone ON EmployeeCellPhoneRelationship.FK_CountyCellPhone = CountyCellPhone.PK_CountyCellPhone
GROUP BY PK_EmployeeCellPhoneRelationship, Employee.LastName, Employee.FirstName,
                           Employee.MiddleName, Employee.Suffix, CountyCellPhone.CellPhoneNumber
ORDER BY Employee.LastName, Employee.FirstName, Employee.MiddleName, Employee.Suffix,
                           CountyCellPhone.CellPhoneNumber

-- Sum of AmountPaid
SELECT     Employee.LastName, Employee.FirstName, Employee.MiddleName, Employee.Suffix,
                  CountyCellPhone.CellPhoneNumber, SUM(AmountPaid) AS TotalPaid
FROM         PaymentHistory
                   INNER JOIN EmployeeCellPhoneRelationship ON EmployeeCellPhoneRelationship.PK_EmployeeCellPhoneRelationship = PaymentHistory.FK_EmployeeCountyCellPhoneRelationship
               INNER JOIN Employee ON EmployeeCellPhoneRelationship.FK_Employee = Employee.PK_Employee
               INNER JOIN CountyCellPhone ON EmployeeCellPhoneRelationship.FK_CountyCellPhone = CountyCellPhone.PK_CountyCellPhone
GROUP BY PK_EmployeeCellPhoneRelationship, Employee.LastName, Employee.FirstName,
                           Employee.MiddleName, Employee.Suffix, CountyCellPhone.CellPhoneNumber
ORDER BY Employee.LastName, Employee.FirstName, Employee.MiddleName, Employee.Suffix,
                           CountyCellPhone.CellPhoneNumber

The problem is that I can't figure out how to join the select statements together to get back one record with the name, phone number, total owed, and total paid. I have tried to JOIN the "extra" table that is missing from each statement (BillingHistory or PaymentHistory), but then my SUMs are all wrong.

I think this is a lot easier than I am making it out to be, so hopefully someone can enlighten me.