Mark-101232 (10/1/2013)

---

dwain.c (9/30/2013)

---

Mark-101232 (9/30/2013)

---

WITH CategoriesWanted([Category]) AS (

SELECT [Category]

FROM ( VALUES (3),(6)) x([Category])

)

SELECT [Project]

FROM [dbo].[Project_Category]

WHERE [Category] IN (SELECT [Category] FROM CategoriesWanted)

GROUP BY...

To add onto Luis's suggestion:

WITH SampleData AS (

SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL

SELECT 'Some invalid@email addresses can also be removed...

Perhaps like this?

with t as (

select 100 pk, 1 level, 'Alpha' c1, 'ID101' c2 union

select 100 pk, 2 level,...

There's about a 50-50 chance that this article may give you some ideas how you could do this:

http://www.sqlservercentral.com/articles/Excel/91179/

SELECT CAST(N'15 Jul 2013' AS DATE)

<Ctrl-Alt-Delete>US Federal Government</Ctrl-Alt-Delete>

Could be the best thing to happen in years.

Living in Thailand, I'm hoping our government follows suit.

SQL_FS (10/1/2013)

---

Sean Lange (10/1/2013)

---

This is NOT...

I believe...

If that still wasn't fast enough, this one should blow the doors off of both.

SELECT a.[Contract], a.AdmissionDate, Cost=SumofCost, Members=CountofMembers

,SumofCost=CAST(0.0 AS DECIMAL(19,4))

,CountofMembers=0

...

In the meantime, you could also try this version, which provides a slightly different but possibly useable result also.

WITH MyData AS

(

SELECT a.[Contract], a.AdmissionDate, SumofCost, CountofMembers

...

Mark-101232 (9/30/2013)

---

WITH CategoriesWanted([Category]) AS (

SELECT [Category]

FROM ( VALUES (3),(6)) x([Category])

)

SELECT [Project]

FROM [dbo].[Project_Category]

WHERE [Category] IN (SELECT [Category] FROM CategoriesWanted)

GROUP BY [Project]

HAVING COUNT(DISTINCT...

I never use the column positional (I think it is called ordinal) as the SORT BY column.

I think it is much more clear to use the explicit column name, so...

Briceston (9/30/2013)

---

Ok, understood.

I used your last code example in my environment, and it's taking long to execute. I keep having to cancel it at the 4min mark or...

dc.pc100 (9/30/2013)

---

I havent had much experience with Tally tables so do no know how to utilize them.

The "OR" x12 in the where clause it fine for now. Ill probs just...

This might work for a few more cases but no guarantee for all.

WITH SampleData (sourcedata) AS

(

SELECT '12 Jan 2013 Test.docx'

UNION ALL...