Can you post the actual plan rather than the estimated plan?

Can you set up a sample table with a few rows of data? The link in my sig will show you how to do this.

Have you tried normalising out the first column into two, say in a CTE? If the numeric component is always the first four characters, you could do this easily with...

dva2007 (11/2/2011)

---

Just to clarify something for my knowledge, i thought CTE can be replacement of temp tables. I've started using CTEs and it is useful in terms of code readability,...

Same as the original, Jeff - CLR. I use T1 at work because the output of Split() is unexpected with delimiters such as ' of '.

FWIW I tried a number...

Paul's suggestion of piling tons of rows into the recursive part to speed up counting gave me an idea. Well, two ideas. The first was to minimise the activity of...

The Dixie Flatline (11/7/2011)

---

I agree with Chris.

Because I inadvertantly started (this very entertaining and informative discussion) by making a "suckish" comment, I feel compelled to admit that I have...

Read a few of Jeff's articles mate, see for yourself why...

Do you see any difference if you change this...

WHERE (Quote.SaleTransferredTS >= '2010-01-01')

GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS

HAVING LEFT(Comp.CompanyNumber, 6) = '240002')

to...

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,

...

This should get you started:

-- Create some sample data to play with

DROP TABLE #Fullname

CREATE TABLE #Fullname (ID int identity(1,1), Fullname VARCHAR(50))

INSERT INTO #Fullname (fullname)

SELECT 'Phill R Williams' UNION ALL

SELECT 'P...

Beginner_2008 (11/5/2011)

---

how to compare a column from one table with two columns in other table keeping the primary key same in both the tables?

After comparing if the two columns...

Have you tested the solution I posted yet? There's a reason for choosing SUM() over COUNT(*) which I'm expecting you will have to use.

Oh, and it's all an Illusion...

Drop the UNIONs and read the source table only once:

SELECT d.district_nm,

urban_appr = SUM(COUNT_urban_appr),

urban_in = SUM(COUNT_urban_inprogress),

rural_appr = SUM(COUNT_rural_appr),

rural_in = SUM(COUNT_rural_inprogress),

esthal_appr = SUM(COUNT_esthal_appr),

ground_in = SUM(COUNT_esthal_inprogress)

FROM (

SELECT

district_nm,

scheme_type,

COUNT_urban_appr= SUM(CASE WHEN urban_appr IS...

Here's a few preliminary results from queries posted so far, and another which I'm guessing is Jeff's preaggregate. The preaggregate works fastest but not by a fat lot. I've not...