Gohloum (10/24/2012)

---

Thanks for your response and I have read through the thread for creating sample data. However after following the directions for creating my first table, it throws an...

This: -

AND (a3.ORDPROD LIKE 'OTHER%')

breaks your outer join and turns it into an inner join.

Try this: -

SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]

FROM dbo.TABLE1 a1...

Hey and welcome to SSC!

I'd love to help, but I'm afraid that you haven't quite provided enough information. What I could do with from you is readily consumable sample data...

Change your DESC to ASC for your ROW_NUMBER to get them in the correct order.

Why do you want to get rid of the records that are gone? Is it because...

Whoever sent that would go into my "junk" filter for all time.

EamonSQL (10/24/2012)

---

select from the table using the ROW_NUMBER() function ordering by id and partitioning by value.

Wrap in brackets giving you a derived table.

Run a delete command against the table and...

ThomasRushton (10/24/2012)

---

This appears to do what you want:

use tempdb

go

declare @testtable table (id int, value int, name char(4))

insert into @testtable values

(1, 10, 'test'), (2, 5, 'prod'), (3, 10, 'test'), (4, 4,...

davdam8 (10/24/2012)

---

Hi geniuses.

I got a report to build and I need data from a field which contains lots of junk data, like:

MNN.helloworld

SSD.goodbyeworld

DDS.seeulaterworld.oops

....

What I really need when there's only 1 '.'...

That would be because you didn't supply DDL or readily consumable sample data,...

Something like this: -

SELECT

realXML.value('(/Announcement/Headline/BroadcastDate)[1]', 'DATE') AS [Date],

realXML.value('(/Announcement/Headline/BroadcastTime)[1]', 'TIME') AS [Time],

realXML.value('(/Announcement/Full_News/Code)[1]', 'VARCHAR(8)') AS [Code]

FROM (SELECT CONVERT(XML,CAST(XMLData AS NVARCHAR(MAX)), 2)

FROM @t)a(realXML);

--========================--

--== CREATE SAMPLE DATA ==--

--========================--

SELECT yourData

INTO #yourTable

FROM (VALUES('MNN.helloworld'),('GFF.goodbyeworld'),('SSW.seeyoulaterworld.oops')

)a(yourData);

--========================--

--== SOLUTION ...

dwain.c (10/16/2012)

---

I recommend that you take heed of Jeff's question above. An improper JOIN often generates tons of duplicates and developers can be too lazy to understand why and...

dwain.c (10/16/2012)

---

Cadavre - Can you provide the sample data you got this result with?

When I run mine on the OP's sample data I get this:

DETAILS_IDCol1Col2Col3Col4

EA22749B180C41D09B4CC986D21C8F0211/28/200601/01/19001PL625245

BTW. Changing the CONVERT format...

Lowell (10/16/2012)

---

ok, adding WITH SCHEMABINDING to my scalar function cuts half a second off of my performance, so it goes to 7.5 seconds or so instead of over 8 seconds....

sj999 (10/16/2012)

---

I have a table TXNS

Column A is int (3)

Column B is varchar 10

I wish to remove all non-alphanumeric characters from Column B

using a tally table.

Lets set a limit of...