Dwain Camps wrote this great article about this recently which was consistent with my experience using windows functions: The Performance of the T-SQL Window Functions[/url]. I suggest giving it a...

This should get you what you need. Note my comments...

-- global temp table can be accessed from inside a string

IF OBJECT_ID('tempdb..##xmlTemp') IS NOT NULL DROP TABLE ##xmlTemp;

GO

--declare @xml nvarchar(max)-- You...

Nevyn (2/10/2014)

---

Yep. Was trying...

Had a few minutes to kill... Here's a solution using what is commonly referred to as the "Quirky Update". I included the code to make sample data...

USE tempdb

GO

IF OBJECT_ID('tempdb.dbo.dimdate')...

First, Cudo's to Google Chrome. I just started typing this, then lost power. When I rebooted and opened Chrome my comment was still here.:w00t:

Below is a better way to create...

Luis. Thanks for putting all this together; I wanted to reply soon but it's been a busy couple days.

I have been playing around with the code you posted and...

Luis Cazares (2/4/2014)

---

... Why wouldn't you want to use a nice pre-aggregated cross tab approach?

SELECT

SUM(CASE qtr WHEN 1 THEN sales END) AS q1,

SUM(CASE qtr WHEN 2 THEN sales END) AS...

Luis Cazares (2/4/2014)

---

Hi Alan,

You need to change your table to a subquery involving only the grouping columns and the ones to be aggregated.

SELECT [1] AS q1, [2] AS q2, [3]...

Ditto what Sean said. There's many people who want to help but we just can't based on what you have provided thus far.

That said, I am going to take...

This is a great blog post/series! I don't know if this helps the OP...

Sowbhari (2/4/2014)

---

This might not be the most elegant way but give it a try

SELECT StartDate =

CASE WHEN CONVERT(VARCHAR(8),@Trandate,112)

BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)) + '0406'

AND CONVERT(VARCHAR,YEAR(@Trandate)+1) + '0405'

THEN CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@Trandate)) + '0406')

WHEN CONVERT(VARCHAR(8),@Trandate,112)

BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)-1)...

Below is a T-SQL script I created last month for this kind of thing. Note my comments, you will have to make a couple changes for this to work.

1)...

This can still be optimized (I'm out of time here). But this should be an enormous improvement.

WITH CTE AS

(

SELECT Usr,

CASE WHEN val1=1 THEN 1 END AS val1,

CASE WHEN...

I am looking at this. Is it possible for you to post the actual execution plan that is created when you run this query?

Sean Lange (1/31/2014)

---

Alan.B (1/30/2014)

---

Well done!...