ChrisM@Work (12/6/2011)

---

Now imagine you're writing a query to output cleaned title, firstname, lastname from a table of appallingly bad data. You've set up a shedload of detection and cleaning rules....

ChrisM@Work (12/6/2011)

---

drew.allen (12/5/2011)

---

ChrisM@Work (12/5/2011)

---

You might get one or two ideas from the following. It's proving very usable for data cleansing, which is similar to your scenario - you want to...

Your approach to SQL training is fundamentally wrong. Without specifics on what you think is fundamentally wrong, why you think it is...

ChrisM@Work (12/5/2011)

---

You might get one or two ideas from the following. It's proving very usable for data cleansing, which is similar to your scenario - you want to identify a...

No, but you can have a correlated subquery, a (NOT) IN with a subquery, or a (NOT) EXISTS with...

aarionsql (12/5/2011)

---

Hi Jim,

Many thanks for your help. I should have mentioned I have gone down the route of CASE statements .. but there are far too many columns to look...

Jim-720070 (12/5/2011)

---

Hi,

How many fields do you need to look at? If there aren't too many you could use a CASE statement, given you work the logic out correctly:

SELECTEmp_No,

HR_ID,

CASE

WHEN LEN(Emp_No)...

aarionsql (12/5/2011)

---

Hi Lowell,

Many thanks for the code .. I am looking to loop as I will have a number of such columns and I will want to concatenate the error...

It also helps to include expected results based on your sample data and what you have already tried. This looks fairly straightforward to me, so I must be misunderstanding...

In case you hadn't noticed,...

Then why create one?...

MyDoggieJessie (11/30/2011)

---

Do you have specific concerns with using the temp tables? Or are you seeing...

It looks like you're going to need to use an aggregate function with windowing. The simplest case would be

CASE WHEN MAX(Status_ID) OVER( PARTITION BY Order_Number ) > 40 THEN...

The Dixie Flatline (11/29/2011)

---

A good carry-away lesson from this is that set-based solutions are possible, even easy, whenever you can define the sets properly. The failed solution...

I've been thinking about this, but hadn't come up with a workable solution until now.

WITH Remaining_Prods AS (

SELECT p.ID

,p.[Name]

,Row_Number() OVER ( ORDER BY p.Sort ) AS Seq

FROM Prods AS p

LEFT...