SQL Gurus... HELP!!!

[JoshWithrow]

I have a SQL database. I have a scenario I want to pose to anybody who's willing to give this a shot. I'm working with three tables.


Employees
=============
ID
First
Last
Office (same as [Offices.Name])


Offices
=============
ID
Name


PostcardTracking
=============
ID
Agent (same as [Employees.ID])
Office (same as [Offices.Name])
mListQty


I need to display this information (in a web report - don't include details about formatting or anything):

Office | #Employees/Office | Mailing | Total Pieces



This is what needs to happen... I need to display each office name once in the Office field of the web report. Along with each office I need to display the number of employees in the office (each is in the Employees table only one time), the % of Employees that show up in the PostcardTracking table per office, and the total pieces of mail (mListQty) sent from users in that office.

I need to build this information into rows (JOIN) so I can output it to a dataset and write it to screen.

This seems like a trivial task, but my mind has come to a halt and im just totally stumped... please help!!!

 

[twifosp]

What is mailing supposed to output?

Other than that, you are going to create a query something to the effect of:

select	o.office,
	count(o.agent),
	sum(p.mlistqty)
from	office o
join	postcartracking p
on	o.id = p.id
group by office

 

[LogicalmanUS]

JoshWithrow,

The below code does 75% of your needs.
Unfortunately I'm unclear on exactly what you need in respect of '% of Employees that show up in the PostcardTracking table per office'.
Can you give a further explanation as to exactly what you need. Is it the % of times an Office appears in the PostcardTracking table upon the total number of items by Employees of that Office?

Well, here most of the code for you:


SELECT o.Name AS Office, COUNT(e.ID) AS Emps, SUM(p.mListQty) AS posts FROM aPostcardTracking p RIGHT OUTER JOIN aEmployees e ON p.Agent = e.ID RIGHT OUTER JOIN aOffices o ON e.Office = o.Name GROUP BY o.Name

Logicalman

 

[JoshWithrow]

Every employee entered into the Employees table has the possibility of being in the PostcardTracking table also. What I need to know is:

Per office, how many of the employees in that office are also in the postcardtracking table.

 

[LogicalmanUS]

JoshWithrow,

Try this one then:

SELECT
o.Name AS Office,
COUNT(e.ID) AS OEmpCount,
SUM(p.mListQty) AS OPosts,
(select count(Agent) from aPostcardTracking where Office = o.Name GROUP BY Office ) AS PEmpCount
FROM aPostcardTracking p
RIGHT OUTER JOIN aEmployees e
ON p.Agent = e.ID
RIGHT OUTER JOIN aOffices o
ON e.Office = o.Name
GROUP BY o.Name

where the following is true:
OEmpCount = # of Employees per Office in Employees
OPosts = # of Posts per Office in aPostcardTracking
PEmpCount = # of Employees per Office in aPostcardTracking

Unfortunately it's not feasible to complete the calculation to get the percentages in the same query as it would mean having an aggregated sum inside a subquery, which SQL tends to hate!

Logicalman

 

[JoshWithrow]

Yeah, i gotcha on that last part. I'll just let .NET do that talking.

It worked fine Thanks much, Logicalman!!!