HELP! SQL Gurus Needed!

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!!!:: the % of Employees that show up in the PostcardTracking table per office
can you xplain a little more of this..

also, you might want to change the 'Id' in each table to the appropriate Id
employees_Id, office_id, postcard_id to avoid confusion about the ids. also i think Id is key word.|||Ok, so I'm not a SQL Guru but luckily you don't need one.

For the number of employees per office:

select O.ID as OfficeId,
O.Name as Office,
count(*) as [Employees/Office]
from Offices O
join Employees E
on O.Name = E.Office
group by O.ID, O.Name

For the percentage of employees that's tracking postcards (whatever that means):

select O.ID as OfficeId,
O.Name as Office,
(select count (distinct Agent)
from PostcardTracking T
where T.Office = O.Name)
/ count(*) * 100 as Mailing
from Offices O
join Employees E
on O.Name = E.Office
group by O.ID, O.Name

For the total number of pieces modify the first query. Then whack them all together any way you want - you can use one big select, or a temp table - whatever your brain can make work. My solution assums you don't want to see offices with no employees, which is probably a fair assumption.

Couple of things please:
1) Normalise your DB
2) Use OfficeID instead of just ID
3) Use singulars for table names, because if you think about it all tables will have more than one row and so end up all being plural. Use singulars and you save yourself from typing an 's' everytime you use a table.
4) Add some foreign key constraints to your tables. How do I know you don't use them? I'm a clairvoyant.
5) Normalise your DB.|||Thanks, Pierre.

I appreciate the tips. Most of what's there was there when I got where I am ;) None the less, I'm new at database design/engineering and have learned a bunch in the short amount of time that i've been doing this. Your input is much appreciated.

And thanks for the help with the queries :) they did the trick!!!

Help with Web Sync sql 2005 to sql express

Hello,

OK I finally got the subscriber connected to the IIS server for replication. I am now getting errors when trying to apply the snap shot. Below is the error? Did I setup the publication incorrectly by selecting replication with another sql 2005? Am I supposed to select something different when trying to replicate between slq 2005 and sql express?

Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'activities_2.sch' could not be propagated to the subscriber.
2005-08-24 20:52:35.920 Percent Complete: 0
2005-08-24 20:52:35.920 Category:NULL
Source: Microsoft SQL Native Client
Number: 1703
Message: Online index operations can only be performed in Enterprise edition of SQL Server.

'activities_2.sch' script

drop Table [dbo].[activities]
go
SET ANSI_PADDING ON
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activities](
[activity] [varchar](50) NOT NULL,
[billing] [bit] NOT NULL,
[category] [varchar](50) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_77F8C0F06FB942A7B7206EF4GD99AD745] DEFAULT (newsequentialid())
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[activities] ADD CONSTRAINT [PK_activities] PRIMARY KEY CLUSTERED
(
[activity] ASC
)WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
GO

I've experienced the same problem but am struggling with the bitwise syntax for disabling the XMLIndex schema option using sp_changemergearticle. Could you provide an example?

Also, as an alternative workaround during development I've been manually commenting out the problem index from the .dri and .sch files in the snapshot:

--WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

Obviously it would better to fix the XMLIndex schema option than to have to do this everytime I create a new snapshot!

Thanks, STEVE

|||The current schema option should be something like: 0x04nnnnnn
To remove the XML index, make it 0x00nnnnnn and run snapshot again and sync (reinit).

If it is 0x07nnnnnn, then make it 0x03nnnnnn.
Basically you want to remove the 04 part in it.|||The current schema options on my tables is :

0x000000000C034FD1

I tried changing it to

0x000000000C030FD1

but that didn't work.

Any ideas?

|||The current schema option: 0x000000000C034FD1
New one to try: 0x0000000008034FD1|||This was a known problem with Express subscribers.
Which CTP are you using? This should be fixed in the further CTPs. Either try the next CTP or there is a workaround below:

Meanwhile you can workaround the issue by disabling the XMLIndex schema option (0x04000000) on the table articles.

Use sp_changemergearticle to change the schema_option to remove this and then the snapshot should be applied correctly.