First, I do believe you can do this with using LAG. I don't have access to a 2012 instance at the moment; otherwise I could post an example.

That said,...

SQLRNNR (2/19/2014)

---

Great article. Very useful information.

One question I think could be answered and would be very useful to those noobs to CLR.

How do you get the varbinary representation for...

5 stars! I just finished this and really enjoyed it. This is turning into a very excellent stairway Solomon and I can't wait to read the rest of the "levels"....

Luis Cazares (2/7/2014)

---

...you're cheating with your test. You didn't clean up the buffer after the cross tabs and before the pivot. At least not in the code you posted. That...

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)...