Merging SQL Select Statements?

Last Post 19 Sep 2007 05:44 AM by SwePeso. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
nnnnnpatel
New Member
New Member

--
18 Sep 2007 07:44 AM
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.

thanks
AF
cehepker
New Member
New Member

--
18 Sep 2007 10:30 AM
What if you make the employee file the main file and join from it to both the payment history and the billing history files? I have not tried this, it is just a thought.
SQLUSA
New Member
New Member

--
18 Sep 2007 10:21 PM
You can just do a UNION between the 2 sets. Set the calculated column 0 where it is not calculated.

Kalman Toth, MCITP (DBA, DBD, BID)
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandprix
SwePeso
New Member
New Member

--
19 Sep 2007 05:44 AM
SELECT Employee.LastName,
Employee.FirstName,
Employee.MiddleName,
Employee.Suffix,
CountyCellPhone.CellPhoneNumber,
SUM(x.AmountOwed)AS TotalOwed,
SUM(x.AmountPaid) AS TotalPaid
FROM (
SELECT FK_EmployeeCountyCellPhoneRelationship,
AmountOwed,
0 AS AmountPaid
FROM BillingHistory

UNION ALL

SELECT FK_EmployeeCountyCellPhoneRelationship,
0 AS AmountOwed,
AmountPaid
FROM PaymentHistory
) AS x
) AS x
INNER JOIN EmployeeCellPhoneRelationship ON EmployeeCellPhoneRelationship.PK_EmployeeCellPhoneRelationship = x.FK_EmployeeCountyCellPhoneRelationship
INNER JOIN Employee ON EmployeeCellPhoneRelationship.FK_Employee = Employee.PK_Employee
INNER JOIN CountyCellPhone ON EmployeeCellPhoneRelationship.FK_CountyCellPhone = CountyCellPhone.PK_CountyCellPhone
GROUP BY Employee.LastName,
Employee.FirstName,
Employee.MiddleName,
Employee.Suffix,
CountyCellPhone.CellPhoneNumber
ORDER BY Employee.LastName,
Employee.FirstName,
Employee.MiddleName,
Employee.Suffix,
CountyCellPhone.CellPhoneNumber
You are not authorized to post a reply.

Acceptable Use Policy