Here's one way...

declare @myTable table (firstname varchar(10), lastname varchar(10), itemType varchar(10), created datetime)

insert @myTable

select 'john', 'doe', 'B', '2008-04-01'

union all select...

karthikeyan (4/25/2008)

---

Thanks a lot Ryan ! Really it opens my eyes broadly.

CBAC - Context Based Access Control which is related to Network protocols.

what do you mean by TBAT ? I...

This will work for small databases in SQL 2000 (8000 characters is limiting here). Adapt to a loop for larger databases.

declare @s-2 varchar(8000) --use varchar(max) in SQL 2005

select @s-2 =...

I looks like CBAC logic to me.

Anyway, I've just knocked this up which should be a pretty efficient approach. It's...

select * from myTable where BatchID not in (select BatchID from myTable where Status = 'Rework')

Sounds painful.

Anyway, here's someone who asked the same question...

http://www.sqlservercentral.com/Forums/Topic480120-9-1.aspx

Check out this article...

http://www.sqlservercentral.com/articles/Advanced+Querying/exoticsqlnullcolumnanalysis/1442/

Or maybe even UNPIVOT, given the underlying data you seem to be describing there.

You should seriously consider storing your underlying data in a normalized structure...

http://support.microsoft.com/kb/283878

No! Look it up...

http://msdn2.microsoft.com/en-us/library/ms177562.aspx

Yep - it has it's limitations. I appreciate you doing the test. Many thanks 🙂

GSquared (4/22/2008)

---

Ryan, that will work if you want to get rid of them completely. What's needed here is eliminating duplicates, but keeping one per set. For that, it...

GSquared (4/22/2008)

---

Okay, now that I got the regex working (more accurately, now that I stopped making stupid mistakes while trying to use it), I got some figures on speed:

On the...

Since we're discussing the various methods, here's a related excerpt from BOL:

The core of the SQL Server 2005 system catalogs is a set of views that show metadata that describes...

Ah! The 'null' records are just the totals! It's all fine... 🙂

http://www.sqlservercentral.com/Forums/TopicSubscriptions1.aspx