Glad I could help 😀

And thank you for the feedback.

scott.pletcher (11/11/2010)

---

Quite. ...

scott.pletcher (11/10/2010)

---

You might add one further refinement to Lutz's excellent query, depending on whether Results could ever have other values:

SELECT

COUNT(*)...

CELKO (11/10/2010)

---

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow...

Two things:

#1 (less important)

The thread you "hijacked" is almost 7 years old... It's usually better to open a new thread and to add a link to the old one for...

You might want to have a look at Jacob Sebastians blog over at beyondrelational.com

Karthik,

you've been around long enough to know how to ask questions. Table DDL and sample data in a ready to use format, expected output and what you've tried so far....

Instead of the additional index I'd replace the multiple SELECT with a single SELECT. Something like

SELECT

COUNT(*) AS TotalNumberOfUnits,

SUM(CASE WHEN Results = 0 THEN 1 ELSE 0 END) AS T1,

SUM(CASE...

Here are two great resources that should help you getting started:

link 1

link 2

Jack Corbett (11/5/2010)

---

...

Diagnosis is in. Displaced fracture about 2 inches below the elbow which requires surgery to be set properly. Local ortho doesn't do kids (which makes me...

Do you have any column in your table to identify a single row? (e.g. an identity column?)

If so, you could use a subquery to get either the min or max...

Glad I could help 😀

If you get stuck with your next challenge, post back (or open another thread if not directly related). 😉

Good luck!

I second Michael.

Why not use the update directly?

UPDATE sample_table

SET CLEAN_POSTCODE = SUBSTRING(postcode, NULLIF(PATINDEX('% ' + format + ' %', ' ' + postcode + ' '), 0), LEN(format))

Edit: or are...

I'm not sure if you're looking for a "static" function (in the meaning of returning the first day of a week regardless of the DATEFIRST setting)...

In this case you could...

several options

1) change the data source to provide clean data to begin with (I know, most probably unlikely to happen, but that's the ideal scenario 😉 )

2) SELECT DISTINCT...