ccavaco (9/19/2013)

---

Lol, CASE -

SELECT

AlternateName = CASE WHEN AlternateName = ' ' AND AlternateName1 <> ' '

THEN AlternateName1 WHEN AlternateName = ' ' AND...

Avoiding DISTINCT and perhaps a little simpler logic is:

SELECT TransactionNumber

FROM (

SELECT TransactionNumber, CancelDueToCorrection

,rn=ROW_NUMBER() OVER (PARTITION BY TransactionNumber ORDER...

PatternSplitCM (see the 4th article in my signature links) is a high performance tool to facilitate tasks such as this one. Makes the code look pretty simple too.

WITH SampleData...

marlon.seton (9/19/2013)

---

dwain.c (9/18/2013)

---

Thanks! From your...

ChrisM@Work (8/28/2013)

---

John's correct, here's another way:

SELECT

col1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END),

col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END),...

Jeff Moden (9/18/2013)

---

Sean Lange (9/18/2013)

---

Since nobody else suggested it I guess I feel that I should. A more long term fix for this would be to normalize your data instead...

mickyT (9/18/2013)

---

dwain.c (9/18/2013)

---

How about using a string pattern splitter?

WITH SampleData (MyStr) AS (

SELECT 'Ørstedsvej 7B' UNION ALL

SELECT 'Volvo 25D' UNION ALL

SELECT '104ND Nokia'

)

SELECT MyStr

...

I believe that if you're exclusion date ranges do not overlap, you may also be able to do it like this:

SELECT OrderID,[MaxNonExclusionDays]=MAX([NonExclusionDays])

FROM (

SELECT OrderID, GapStart=MIN(GapDates),...

How about using a string pattern splitter?

WITH SampleData (MyStr) AS (

SELECT 'Ørstedsvej 7B' UNION ALL

SELECT 'Volvo 25D' UNION ALL

SELECT '104ND Nokia'

)

SELECT MyStr

...

Yet another way using Jeff Moden's venerated delimited string splitter: DelimitedSplit8K[/url] (download at the link provided):

declare @MyValue varchar(1000) = '<NewOrder><InstID>ED</InstID><PatientID>22164</PatientID><LName>fACKSON</LName><FName>JAMES</FName><EntryID>1923236</EntryID><OrderID></NewOrder>'

SELECT LEFT(Item, PATINDEX('%[^0-9]%', Item)-1)

FROM (SELECT REPLACE(@MyValue, 'EntryID>', CHAR(5))) a(MyStr)

CROSS APPLY dbo.DelimitedSplit8K(MyStr,...

Perhaps like this?

UPDATE a

SET MyTotUnis = ISNULL(

(

SELECT TOP 1 Units

FROM...

Thanks! From your information it...

marlon.seton (9/17/2013)

---

dwain.c (9/16/2013)

---

marlon.seton (9/16/2013)

---

We write all our code in Oracle first, where PL/SQL is needed, then produce the tSQL version afterwards where that is different, so we'd never have this...

I see you accepted it now for publication. That's cool!

I noticed that I misspelled "Spackle" as "Spacklet" in the title. I hope you can correct that before publication.

I...

Sean Lange (9/16/2013)

---

dwain.c (9/15/2013)

I don't recommend doing the REPLACE in the call to DelimitedSplit8K function. Even though that is an awesome tool, I've run into some unexpected performance issues...