vip.blade (2/6/2014)

---

Wow, thats amazing, thanks alot! I never thought of using a CASE-Statement in a SUM() function. That helped me very much!

Is there also a way to use the FIRST_VALUE(order_date)...

I'm glad to hear it helped too. It got me to thinking that this is sort...

David McKinney (2/6/2014)

---

Damned if you do, damned if you don't!

I find the criticism somewhat harsh. ...

Also, sorry but I find the allusion to plagiarism a little facile....

Sean Lange (2/5/2014)

---

Dwain you hit both things I was thinking myself.

To be fair there are two splitters posted. The first is the looping version. The second one is inside...

Alex,

Unfortunately, I must offer two criticisms of this article. At the end you said:

Alex Grinberg (1/15/2014)

---

My preference is the User-Defined Table type. I found that this technique is better...

Actually, I am not too proud to admit that solution stunk up the house! Not sure what I was thinking there.

This will be much faster,...

Only if...

Agreed...

I agree that your expected results don't match your sample data. But I think you're looking for something like this:

SELECT id, cid, startdate=MIN(d)

, enddate=CASE

...

Lowell has provided you a perfectly good solution to your question. However, you may want to also consider something like the following for performance reasons:

WITH PreAggregate AS

(

...

Jeff Moden (2/5/2014)

---

He should come to Michigan. It was snowing to beat the band a couple of days ago and...

I'm just guessing here because I don't have much additional time to look deeper, but this probably has to do with the fact you have no "supersession date" on your...

JohnFTamburo (2/4/2014)

---

A nonclustered index scan on the column is no major advantage. You still have to traverse every value in the index. Depending on the width of the table,...

p.barwick (2/4/2014)

---

This, for example, returns 0 rows for the last segment.

..

CREATE TABLE #Source

(

PartNo VARCHAR(20)

,PartDesc VARCHAR(100)

...

This code gets me a non-clustered INDEX Scan on 2008 R2 and 2012.

CREATE TABLE #TempTab

(

ID INT IDENTITY PRIMARY KEY

,PhoneNum VARCHAR(10)

...