• You have non-SARGable formulas in your WHERE clause.
• You call the same table multiple times when you probably only need to call it once.
• You haven't posted the execution...

My first thought is unprintable characters, e.g., tabs.  I usually cast to binary to see if the binary data matches what I'm expecting.

Drew

You're overlooking the obvious.  There is a QUARTER date part.  This becomes simple if you use that.

CASE WHEN DATEDIFF(QUARTER, c.DATEKEY, @TodaysDate) = 0 THEN 1 ELSE 0...

I didn't round my results, but otherwise this gives the same results.  I also had to add extra values to the table to get all of the dates that you...

I see you updated the LAST_VALUE(), but not the FIRST_VALUE().  The default isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT...

It's simple enough to test.  Have you done so?

Drew

So just a couple tips for the next time you post.  Make sure you put any DDL statements in a code block so its easier to read.  Most people...

CREATE table #AllCountryStates ( CountryName NVARCHAR(100), Treeid int ) insert INTO #AllCountryStates select N'??',1 select * from #AllCountryStates declare @Country NVARCHAR(1000) SELECT @Country = STUFF( (SELECT ',' + '

Hi all, Been going mad most of today with trying to retrieve CoverType from the below XML data: If anyone could point me in the right direction it would...

An rCTE is probably one of the worst ways to build a set of intervals.  You are much better of using a tally table with LEAD() or LAG().

J. Drew Allen

Business Intelligence Analyst

Philadelphia, PA