Ahh gotcha. Don't understand the usefulness of something like this but this is how you would do the query.

select ActNo, max(case when ProcessMonth = '2012-Jul' then PlanName else '' end)...

Is it as simple as this?

select ActNo, PlanName, COUNT(*)

from #tempo1

group by ActNo,PlanName

order by ActNo

Lynn Pettis (another long time poster around here) pointed out there is a LOT more wrong with your query. I didn't bother to look past the hideous formatting but he...

You need to update stats and rebuild your indexes.

It looks you have built both versions. Have you tested them? I would recommend nesting all of your replaces instead of doing them one at a time over and over....

Ahh it is a different dataset. I used the original one from the OP. 😛

dwivedi.neeraj's approach will work pretty much....

dwivedi.neeraj (4/9/2013)

---

May be we both are working on different data set.

Here is mine.

create table playground.sale (email varchar(255), quantityint);

insert into sale values ('email1', 1);

insert into sale values ('email1', 2);

insert into...

PSB (4/9/2013)

---

Ye s we will need to consider location,Site and Code while eliminatong

I want to eliminate PrevTrans 1,2,9 because for the same site,location and code because PrevTrans 2 and 9...

Lynn Pettis (4/9/2013)

---

Okay, now I am totally lost. Given a specific date (getdate() in this case), return...

I realize that part of this is...

This will change year to year. What year do you want?

--EDIT--

Bitten by the quote bug.

dwivedi.neeraj (4/9/2013)

---

See you are getting 24 because of cartesian.

So if you use

select sum(distinct(s.quantity))--,s.email

from sale s

inner join eval e

on s.email = e.email

where s.email = 'email1'

if will give you correct result....

Try this as a starting point. The problem is you don't have distributed transactions enabled.

http://stackoverflow.com/questions/1258983/problems-with-msdtc-remote-transactions

A google/bing search of your exact error message returns hundreds and hundreds of explanations of how...

Sounds to me like you are trying to use SUM when what you really want is a different aggregate. Maybe MIN, MAX or even simply "top 1 order by something"...