Ahh yes... Sorry, I did not look at your results completely. You may want to look at the PIVOT capabilities of SQL Server. Personally, I prefer to picot...

erics44 (5/16/2012)

I have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used...

I think the problem lies in your point #1:

But issue with this option is I don't have access to client's machine and if I use DB from some specific version...

...so might be that...

Thanks for posting all of the pertinent information! Since you are learning, I don't want to simply provide you with a script. So, are you familiar with a left...

Assuming it is indexed properly, 10 million records should not be an issue. Let's say the join condition is called ID... Just do a select from the target table...

We keep trying to fix your code, but you keep posting the same thing. We can't help you if you keep posting the same thing over and over.

Ok, do you know how to join to the employment status table? Can you please give us the table definitions for the tables? That is what I asked for.

You're missing the most important parts 🙂 Table definitions for your tables and sample data. This way, we can simulate on our own machines. Sample data does not mean...

OH!!! I got it now!

SELECT AgencyID, AgencyName,AgencyType,

SUM(CASE WHEN isOpenAllegation = 1 THEN 1

ELSE 0 END) AS countAllegations,

...

I just altered the code from my previous post. Please try that.

This:

Select AgencyID, AgencyName,AgencyType, Count of all docs for this AgencyID where

isOpenAllegation= 1, Count of all docs...

Add an END to your CASE statement.

Hmm... Can't be done. http://www.brentozar.com/archive/2008/10/sql-server-2008-management-studio-group-execute/ toward the bottom. It is because the SSMS "feature" sends this query to each server individually. Sorry!

Did you try the server name instead f just the 3 part naming for the INSERT INTO? Not sure that it will work, and if it doesn't then I don't...

Start with this:

;WITH docs AS (

Select AgencyID, Count(*) As Count3yrDocs

From LLA_Audits

Where IsOpenAllegation = 1

GROUP BY agencyID),

scsdocs AS (

Select AgencyID, Count(*) AS CountScsDocs

From LLA_Audits

Where WorkType = 'Agreed-upon Procedures'

GROUP BY AgencyID

)

SELECT a.AgencyId,...