You need to include a CASE statement in your HAVING clause.

Select @constituency, o.customer_no

, Count(l.per_no) AS AllPerf

, COUNT(CASE p.season WHEN 165 THEN l.perf_no...

But this will give you negative numbers from Dec. 26-31. Here is the modified code to always give you the NEXT Christmas.

select DateDiff(Day, GetDate(), dateadd(yy,datediff(yy,-1,DateAdd(Day,...

Maybe what you really want to do is create a view. That way you can use the trick of specifying the TOP (100) PERCENT, so that you can specify...

This is exactly what the PIVOT operator was designed for. Check it out in BOL.

That being said, using a crosstab is often more efficient.

select datepart(ww, created) as Week,

COUNT(CASE s.commonname...

I think the best measure is probably

(totalRecords - uniqueRecords)/totalRecords

In your example that would give 75%. The hard part is, of course, identifying the unique records.

Drew

I don't see that how often it is executed has any bearing on the issue unless it starts blocking itself. I wouldn't immediately target the FOR XML as being...

Do you have a personal vendetta against the FOR...

The "/" only goes in the closing tag. You have it in both the opening...

ravimodi (10/12/2010)

---

Thanks Craig...for your input. Well, I checked this and just wanted to discuss...basically the query u gave gives the month end records and what i need basically are the...

You beat me to it, but I'm posting mine anyhow, because there are some points that I wanted to address.

WITH Charges AS (

SELECT Account, Sum(qty*amount) AS Total_Charges

FROM test_charges AS tc

INNER...

Derrick Smith (10/12/2010)

---

This part is the issue:

AND (NOT email IN (SELECT email FROM failures))

AND (NOT email IN (SELECT email FROM removes))

AND (NOT email IN (SELECT email FROM contacts));

I'd be willing...

aniccadhamma (10/12/2010)

---

regarding DISTINCT... I tried a SELECT DISTINCT email, business statement, but it wanted both email and business together to be distinct. that's not what I want. ...

PSB (10/11/2010)

---

I am using SSIS lookup transform to match an ID corresponding to the name.

The condtition is if the "Name" comes in as NULL then the "ID" should be NULL

Else...

You may have duplicates in your SELECT statement. Adding a DISTINCT clause may resolve this.

Drew

SSSolice (10/11/2010)

---

Thanks for your reply Drew. I did remove the redundant call to tblControlLog but the performance against the presentation data is still too sluggish. Any help with a new...