With the battles I'm currently facing at work on this...

VegasL (1/15/2016)

---

Thanks Luis, the last one appears straightforward, concatenation with fixed characters. However when I try on actual table without the # temp tables, i get incorrect syntax...

wolfgheist (1/14/2016)

---

ChrisM@Work (1/14/2016)

---

wolfgheist (1/14/2016)

---

I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that...

wolfgheist (1/14/2016)

---

I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that contains all...

wolfgheist (1/14/2016)

---

I am playing around with it, but it keeps pulling too much data. It is pulling 20k+ rows, when my first script above pulls 4302 rows that contains all...

wolfgheist (1/14/2016)

---

Hi Chris,

Thanks for the suggestion, but I cannot get that to show what I need.

It is Pulling

MedicalRecordNumberAccountNumberFirstNameLastNameWhenDischarged

12345 ...

Experiment with the result set. Take out the rn=1 filter so you can see the full set.

Have a play with ROW_NUMBER(). This should be close:

SELECT MedicalRecordNumber, AccountNumber, FirstName, LastName, WhenDischarged

FROM (

SELECT p.MedicalRecordNumber, p.AccountNumber, p.FirstName, p.LastName, p.WhenDischarged,

rn = ROW_NUMBER() OVER(PARTITION BY p.MedicalRecordNumber, p.AccountNumber ORDER BY p.WhenDischarged)

FROM Patient...

How do you...blimey Jeff, I...

erics44 (1/8/2016)

---

ChrisM@Work (1/8/2016)

---

erics44 (1/8/2016)

---

Hugo Kornelis (1/8/2016)

---

erics44 (1/8/2016)

---

Hugo Kornelis (1/8/2016)

---

Jeff Moden (1/7/2016)

---

Technically correct because only an rCTE can be an...

erics44 (1/8/2016)

---

Hugo Kornelis (1/8/2016)

---

erics44 (1/8/2016)

---

Hugo Kornelis (1/8/2016)

---

Jeff Moden (1/7/2016)

---

Technically correct because only an rCTE can be an rCTE but...

Jason A. Long (1/7/2016)

---

...

Edit: As a side note... When I included TVFs in that statement, I was referring to the "parameterized view" type of TVF... Not a TVF that's added...

Eric M Russell (1/7/2016)

---

Here you appear to be selecting TOP X rows without an explicit sort order. I'm assuming that ORDER BY (SELECT NULL) is essentially nullifying the sort order,...

Jacob Wilkins (1/7/2016)

---

There have been a few problems like this that seem tied to parallelism. Running with the clustered index and OPTION (MAXDOP 1) yields deterministic results again.

There was this...

There are some code gotcha's in your original query. Looking at the first CTE, and expanding the range of PartID to get a decent sample set for testing, shouldn't this:

SELECT...