data validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have two tables of data, and I want to test the data in one table against
the data in another. Here's the wordy scenario.

Telephone exchanges have exchange service area codes eg: MREE is Moree.
NMEL is North Melbourne. etc Each exchange service area (ESA) has specific
telephone number ranges. There may be many number ranges associates with an
exchange.

For example

Range start Range End ESACode ESAName
92072000 92072199 NMEL NORTH MELBOURNE (VIC)
92075300 92075899 NMEL NORTH MELBOURNE (VIC)
92080200 92080499 NMEL NORTH MELBOURNE (VIC)
67570000 67591799 MREE MOREE
67529300 67529999 MREE MOREE
67511000 67528999 MREE MOREE

I have one table (Table A) with all of this data for alot of exchanges.

I also have another table (Table B) that has a single number, and the
relevant exchange code. Now I am not sure and want to check, if the data in
table B is correct. That is, for that specific number, is the exchange
service area code correct. Or to describe another way, find the range which
the number (from table B) falls in between in table A, check the exchange
service area code in table A against table B, and if it doesn't match I want
to know. The output I would require is the individual phone number that has
a non matching exchange service area code.

Table B looks like this

ExchangeCode Number
MREE 67570000

Therefore in the data example above, I want to ask "which number range does
67570000 fall between, and the answer would be 67570000 - 67591799, and what
is the exchange service area (in table A) for that number range, and the
answer would be MREE, and does that exchange service area (MREE) match the
exchange service area in table B (MREE) and the answer would be yes.

But if the answer was no, I want to output the number and the exchange
service area from table B.

Table A consists of nearly 250K in records. Table B has approximately 18K
in records, therefore doing individual queries one number at a time is just
not viable. I need something that can essentially loop through and test each
number, and where the exchage service areas dont match in the two tables, let
me know.

Any assistance would be greatly appreciated.

Many thanks to all who reply.
John
 
Hello,

I have two tables of data, and I want to test the data in one table against
the data in another. Here's the wordy scenario.

Telephone exchanges have exchange service area codes eg: MREE is Moree.
NMEL is North Melbourne. etc Each exchange service area (ESA) has specific
telephone number ranges. There may be many number ranges associates with an
exchange.

For example

Range start Range End ESACode ESAName
92072000 92072199 NMEL NORTH MELBOURNE (VIC)
92075300 92075899 NMEL NORTH MELBOURNE (VIC)
92080200 92080499 NMEL NORTH MELBOURNE (VIC)
67570000 67591799 MREE MOREE
67529300 67529999 MREE MOREE
67511000 67528999 MREE MOREE

I have one table (Table A) with all of this data for alot of exchanges.

I also have another table (Table B) that has a single number, and the
relevant exchange code. Now I am not sure and want to check, if the data in
table B is correct. That is, for that specific number, is the exchange
service area code correct. Or to describe another way, find the range which
the number (from table B) falls in between in table A, check the exchange
service area code in table A against table B, and if it doesn't match I want
to know. The output I would require is the individual phone number that has
a non matching exchange service area code.

Table B looks like this

ExchangeCode Number
MREE 67570000

Therefore in the data example above, I want to ask "which number range does
67570000 fall between, and the answer would be 67570000 - 67591799, and what
is the exchange service area (in table A) for that number range, and the
answer would be MREE, and does that exchange service area (MREE) match the
exchange service area in table B (MREE) and the answer would be yes.

But if the answer was no, I want to output the number and the exchange
service area from table B.
Click to expand...

SELECT B1.Number, A1.ESACode, A1.ESAName
FROM [Table A] AS A1, [Table B] AS B1
WHERE A1.ESACode = B1.ExchangeCode
AND B1.Number NOT BETWEEN A1.[Range start] AND A1.[Range end];

Jamie.

--
 
Informatics Outsourcing is an Offshore Data Management service company. Data Management Service includes all types of Data Conversion, File Conversion, XML Conversion, HTML Conversion,SGML Conversion, Document Conversion,DataEntry, Data Extraction and Validation,OCR and ICR Services with affordableprice. Our team to give the solution quickly and given requirements.
 
Back
Top