Testing for invalid entries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Testing for invalid entries

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-25 : 13:02:58

I am trying to write a simple script to test that there are no invalid records in a table. The rule is that a PersonID may appear multiple times but if it does then the associated values must all be the same. The following script creates a series of records. The result set is
PersonID Code
01 8
01 8
02 8
02 9

The values for records where PersonID = 01 are valid as Code values are all 8
The values for records PersonID = 02 are not valid because there is an 8 and a 9

Please what is the simplest way to test this over '000s of records?

create table test (PersonID varchar(2), Code varchar(2))
Insert into test (PersonID, Code)
Select '01', '8'
Insert into test (PersonID, Code)
Select '01', '8'

Insert into test (PersonID, Code)
Select '02', '8'
Insert into test (PersonID, Code)
Select '02', '9'

select * from test

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-25 : 13:10:04

-- Get valid
SELECT PersonID FROM Table1 GROUP BY PersonID HAVING MIN(Code) = MAX(Code)

-- Get invalid
SELECT PersonID FROM Table1 GROUP BY PersonID HAVING MIN(Code) < MAX(Code)

N 56°04'39.26"
E 12°55'05.63"

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-25 : 13:30:56

thank you - exactly what I needed - and simple too ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources