eric.lyons (11/7/2011)

---

You can start by 1) adding this to your company table

alter table company

add [account number] as left(companyname,6)

2) Then create the index for it.

3) Change your sql to...

No that was wrong, I found and corrected it after I made the post.

Thank goodness...

Yes, I had errors in my CASE Statement and I corrected them.

I noticed after making the post.

Sorry, i should have edited my post sooner.

I appreciate the help

ChrisM@Work (11/7/2011)

---

[quote-0I'm sorry, I don't understand where the problem is. If you've followed the recipe to the letter then it should work. You don't need to change any CASE expressions...

Edited: I spoke too soon, the problem is with my CASE Statements.

This is unfortunate but when I ran the SQL that included the Temp Table I thought that I was...

It works perfectly, thanks!

ChrisM@Work (11/7/2011)

---

Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:

1.Change FROM #TXActivityTemp to FROM () d

2.Copy...

kramaswamy (11/7/2011)

---

Why not just do this, as your source:

WITH cte AS

(

SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,

MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,

MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,

...

ChrisM@home (11/6/2011)

---

What's wrong with a derived table?

SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,

MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,

MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,

...

Chris,

I had tried that earlier but I made a silly error.

Thanks!:cool:

John Esraelo-498130 (11/6/2011)

---

Based on your scenario and insufficient data given, I may not have the accurate solution for you but I have something herein that might give you and idea...

kramaswamy (11/4/2011)

---

Create a blank template file, and a blank output file. As the first step in your process, use the File System Task, choose Copy File, choose Overwrite Destination, and...

kramaswamy (11/4/2011)

---

Create a blank template file, and a blank output file. As the first step in your process, use the File System Task, choose Copy File, choose Overwrite Destination, and...

Thanks Jack.

That is an excellent article 😎

Jack Corbett (11/4/2011)

---

I'm not sure what you are trying to accomplish. Are you just trying to move the data into Excel?

Can't you just use the SQL Statement in the...