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
 General SQL Server Forums
 New to SQL Server Programming
 Not Like Statement

Author  Topic 

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-08-25 : 14:15:28
Hi Guys,

I am trying to output from all telephone number, those people whose numbers(work,cell or home) do not begin with the prefix (256...259),(260...269) and (820...829). In this database they are many ways that the phone numbers were entered,so I tried to check for as many of them as possible.Also these customers must have made a transaction between May and July.

The problem I am having is that the below code is displaying all customers that do have the above prefixes along with those that don't have the prefixes,when they should not be in the list. Could anyone tell me what is the problem



SELECT

	distinct c.CARDNUMBER
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3 
	,HomePhone
	,CellPhone
	,WorkPhone
	,CardClass
	,c.ClassCode
	,CycleCode
	,Email
	,sum(DailyTransactionValue)TotalSpend 
	,case  when Creditcard = 'y' then 'Y'
			else 'N'
			end IsCredit
FROM
	customer c inner join SubsidiaryByCustomerByClassTransactionDetail ss on c.cardnumber=ss.cardnumber
		left outer join 
		(
			select distinct cardnumber
				,case when count(*) > 0 Then 'y'
					else 'N'
				END Creditcard
			from AlternateAccessKeys
			group by cardnumber
		)data on data.cardnumber=c.cardnumber

WHERE (    (CellPhone not LIKE '246256%')
	or (CellPhone not LIKE '246257%')
	or (CellPhone not LIKE '246258%')
	or (CellPhone not LIKE '246259%')
	or (CellPhone not LIKE '246260%')
	or (CellPhone not LIKE '246261%')
	or (CellPhone not LIKE '246262%')
	or (CellPhone not LIKE '246263%')
	or (CellPhone not LIKE '246264%')
	or (CellPhone not LIKE '246265%')
	or (CellPhone not LIKE '246266%')
	or (CellPhone not LIKE '246267%')	
	or (CellPhone not LIKE '246268%')
	or (CellPhone not LIKE '246269%')
	or (CellPhone not LIKE '246820%')
	or (CellPhone not LIKE '246821%')
	or (CellPhone not LIKE '246822%')	
	or (CellPhone not LIKE '246823%')
	or (CellPhone not LIKE '246824%')
	or (CellPhone not LIKE '246825%')
	or (CellPhone not LIKE '246826%')
	or (CellPhone not LIKE '246827%')
	or (CellPhone not LIKE '246828%')
	or (CellPhone not LIKE '246829%')
				
	or (CellPhone not LIKE '246 256%')
	or (CellPhone not LIKE '246 257%')
	or (CellPhone not LIKE '246 258%')
	or (CellPhone not LIKE '246 259%')
	or (CellPhone not LIKE '246 260%')
	or (CellPhone not LIKE '246 261%')
	or (CellPhone not LIKE '246 262%')
	or (CellPhone not LIKE '246 263%')
	or (CellPhone not LIKE '246 264%')
	or (CellPhone not LIKE '246 265%')
	or (CellPhone not LIKE '246 266%')
	or (CellPhone not LIKE '246 267%')	
	or (CellPhone not LIKE '246 268%')
	or (CellPhone not LIKE '246 269%')
	or (CellPhone not LIKE '246 820%')
	or (CellPhone not LIKE '246 821%')
	or (CellPhone not LIKE '246 822%')
	or (CellPhone not LIKE '246 823%')
	or (CellPhone not LIKE '246 824%')
	or (CellPhone not LIKE '246 825%')
	or (CellPhone not LIKE '246 826%')					
	or (CellPhone not LIKE '246 827%')
	or (CellPhone not LIKE '246 828%')
	or (CellPhone not LIKE '246 829%')

	or (CellPhone not LIKE '256%')
	or (CellPhone not LIKE '257%')
	or (CellPhone not LIKE '258%')
	or (CellPhone not LIKE '259%')
	or (CellPhone not LIKE '260%')
	or (CellPhone not LIKE '261%')
	or (CellPhone not LIKE '262%')
	or (CellPhone not LIKE '263%')
	or (CellPhone not LIKE '264%')
	or (CellPhone not LIKE '265%')
	or (CellPhone not LIKE '266%')
	or (CellPhone not LIKE '267%')	
	or (CellPhone not LIKE '268%')
	or (CellPhone not LIKE '269%')
	or (CellPhone not LIKE '820%')
	or (CellPhone not LIKE '821%')
	or (CellPhone not LIKE '822%')
	or (CellPhone not LIKE '823%')
	or (CellPhone not LIKE '824%')
	or (CellPhone not LIKE '825%')
	or (CellPhone not LIKE '826%')					
	or (CellPhone not LIKE '827%')
	or (CellPhone not LIKE '828%')
	or (CellPhone not LIKE '829%')
	

	or (CellPhone not LIKE '1 246 256%')
	or (CellPhone not LIKE '1 246 257%')
	or (CellPhone not LIKE '1 246 258%')
	or (CellPhone not LIKE '1 246 259%')
	or (CellPhone not LIKE '1 246 260%')
	or (CellPhone not LIKE '1 246 261%')
	or (CellPhone not LIKE '1 246 262%')
	or (CellPhone not LIKE '1 246 263%')
	or (CellPhone not LIKE '1 246 264%')
	or (CellPhone not LIKE '1 246 265%')
	or (CellPhone not LIKE '1 246 266%')
	or (CellPhone not LIKE '1 246 267%')	
	or (CellPhone not LIKE '1 246 268%')
	or (CellPhone not LIKE '1 246 269%')
	or (CellPhone not LIKE '1 246 820%')
	or (CellPhone not LIKE '1 246 821%')
	or (CellPhone not LIKE '1 246 822%')
	or (CellPhone not LIKE '1 246 823%')
	or (CellPhone not LIKE '1 246 824%')
	or (CellPhone not LIKE '1 246 825%')
	or (CellPhone not LIKE '1 246 826%')					
	or (CellPhone not LIKE '1 246 827%')
	or (CellPhone not LIKE '1 246 828%')
	or (CellPhone not LIKE '1 246 829%')

	
	or (CellPhone not LIKE '1-246-256%')
	or (CellPhone not LIKE '1-246-257%')
	or (CellPhone not LIKE '1-246-258%')
	or (CellPhone not LIKE '1-246-259%')
	or (CellPhone not LIKE '1-246-260%')
	or (CellPhone not LIKE '1-246-261%')
	or (CellPhone not LIKE '1-246-262%')
	or (CellPhone not LIKE '1-246-263%')
	or (CellPhone not LIKE '1-246-264%')
	or (CellPhone not LIKE '1-246-265%')
	or (CellPhone not LIKE '1-246-266%')
	or (CellPhone not LIKE '1-246-267%')	
	or (CellPhone not LIKE '1-246-268%')
	or (CellPhone not LIKE '1-246-269%')
	or (CellPhone not LIKE '1-246-820%')
	or (CellPhone not LIKE '1-246-821%')
	or (CellPhone not LIKE '1-246-822%')
	or (CellPhone not LIKE '1-246-823%')
	or (CellPhone not LIKE '1-246-824%')
	or (CellPhone not LIKE '1-246-825%')
	or (CellPhone not LIKE '1-246-826%')					
	or (CellPhone not LIKE '1-246-827%')
	or (CellPhone not LIKE '1-246-828%')
	or (CellPhone not LIKE '1-246-829%')
	
	or (CellPhone not LIKE '1-246256%')
	or (CellPhone not LIKE '1-246257%')
	or (CellPhone not LIKE '1-246258%')
	or (CellPhone not LIKE '1-246259%')
	or (CellPhone not LIKE '1-246260%')
	or (CellPhone not LIKE '1-246261%')
	or (CellPhone not LIKE '1-246262%')
	or (CellPhone not LIKE '1-246263%')
	or (CellPhone not LIKE '1-246264%')
	or (CellPhone not LIKE '1-246265%')
	or (CellPhone not LIKE '1-246266%')
	or (CellPhone not LIKE '1-246267%')	
	or (CellPhone not LIKE '1-246268%')
	or (CellPhone not LIKE '1-246269%')
	or (CellPhone not LIKE '1-246820%')
	or (CellPhone not LIKE '1-246821%')
	or (CellPhone not LIKE '1-246822%')
	or (CellPhone not LIKE '1-246823%')
	or (CellPhone not LIKE '1-246824%')
	or (CellPhone not LIKE '1-246825%')
	or (CellPhone not LIKE '1-246826%')					
	or (CellPhone not LIKE '1-246827%')
	or (CellPhone not LIKE '1-246828%')
	or (CellPhone not LIKE '1-246829%')
	
	or (CellPhone not LIKE '1246256%')
	or (CellPhone not LIKE '1246257%')
	or (CellPhone not LIKE '1246258%')
	or (CellPhone not LIKE '1246259%')
	or (CellPhone not LIKE '1246260%')
	or (CellPhone not LIKE '1246261%')
	or (CellPhone not LIKE '1246262%')
	or (CellPhone not LIKE '1246263%')
	or (CellPhone not LIKE '1246264%')
	or (CellPhone not LIKE '1246265%')
	or (CellPhone not LIKE '1246266%')
	or (CellPhone not LIKE '1246267%')	
	or (CellPhone not LIKE '1246268%')
	or (CellPhone not LIKE '1246269%')
	or (CellPhone not LIKE '1246820%')
	or (CellPhone not LIKE '1246821%')
	or (CellPhone not LIKE '1246822%')
	or (CellPhone not LIKE '1246823%')
	or (CellPhone not LIKE '1246824%')
	or (CellPhone not LIKE '1246825%')
	or (CellPhone not LIKE '1246826%')					
	or (CellPhone not LIKE '1246827%')
	or (CellPhone not LIKE '1246828%')
	or (CellPhone not LIKE '1246829%')
				
	or (CellPhone not LIKE '246-256%')
	or (CellPhone not LIKE '246-257%')
	or (CellPhone not LIKE '246-258%')
	or (CellPhone not LIKE '246-259%')
	or (CellPhone not LIKE '246-260%')
	or (CellPhone not LIKE '246-261%')
	or (CellPhone not LIKE '246-262%')
	or (CellPhone not LIKE '246-263%')
	or (CellPhone not LIKE '246-264%')
	or (CellPhone not LIKE '246-265%')
	or (CellPhone not LIKE '246-266%')
	or (CellPhone not LIKE '246-267%')	
	or (CellPhone not LIKE '246-268%')
	or (CellPhone not LIKE '246-269%')
	or (CellPhone not LIKE '246-820%')
	or (CellPhone not LIKE '246-821%')
	or (CellPhone not LIKE '246-822%')
	or (CellPhone not LIKE '246-823%')
	or (CellPhone not LIKE '246-824%')
	or (CellPhone not LIKE '246-825%')
	or (CellPhone not LIKE '246-826%')					
	or (CellPhone not LIKE '246-827%')
	or (CellPhone not LIKE '246-828%')
	or (CellPhone not LIKE '246-829%')
				
	or (CellPhone not LIKE '(246)256%')
	or (CellPhone not LIKE '(246)257%')
	or (CellPhone not LIKE '(246)258%')
	or (CellPhone not LIKE '(246)259%')
	or (CellPhone not LIKE '(246)260%')
	or (CellPhone not LIKE '(246)261%')
	or (CellPhone not LIKE '(246)262%')
	or (CellPhone not LIKE '(246)263%')
	or (CellPhone not LIKE '(246)264%')
	or (CellPhone not LIKE '(246)265%')
	or (CellPhone not LIKE '(246)266%')
	or (CellPhone not LIKE '(246)267%')	
	or (CellPhone not LIKE '(246)268%')
	or (CellPhone not LIKE '(246)269%')
	or (CellPhone not LIKE '(246)820%')
	or (CellPhone not LIKE '(246)821%')
	or (CellPhone not LIKE '(246)822%')
	or (CellPhone not LIKE '(246)823%')
	or (CellPhone not LIKE '(246)824%')
	or (CellPhone not LIKE '(246)825%')
	or (CellPhone not LIKE '(246)826%')					
	or (CellPhone not LIKE '(246)827%')
	or (CellPhone not LIKE '(246)828%')
	or (CellPhone not LIKE '(246)829%')


	or (HomePhone not LIKE '246256%')
	or (HomePhone not LIKE '246257%')
	or (HomePhone not LIKE '246258%')
	or (HomePhone not LIKE '246259%')
	or (HomePhone not LIKE '246260%')
	or (HomePhone not LIKE '246261%')
	or (HomePhone not LIKE '246262%')
	or (HomePhone not LIKE '246263%')
	or (HomePhone not LIKE '246264%')
	or (HomePhone not LIKE '246265%')
	or (HomePhone not LIKE '246266%')
	or (HomePhone not LIKE '246267%')	
	or (HomePhone not LIKE '246268%')
	or (HomePhone not LIKE '246269%')
	or (HomePhone not LIKE '246820%')
	or (HomePhone not LIKE '246821%')
	or (HomePhone not LIKE '246822%')	
	or (HomePhone not LIKE '246823%')
	or (HomePhone not LIKE '246824%')
	or (HomePhone not LIKE '246825%')
	or (HomePhone not LIKE '246826%')
	or (HomePhone not LIKE '246827%')
	or (HomePhone not LIKE '246828%')
	or (HomePhone not LIKE '246829%')
				
	or (HomePhone not LIKE '246 256%')
	or (HomePhone not LIKE '246 257%')
	or (HomePhone not LIKE '246 258%')
	or (HomePhone not LIKE '246 259%')
	or (HomePhone not LIKE '246 260%')
	or (HomePhone not LIKE '246 261%')
	or (HomePhone not LIKE '246 262%')
	or (HomePhone not LIKE '246 263%')
	or (HomePhone not LIKE '246 264%')
	or (HomePhone not LIKE '246 265%')
	or (HomePhone not LIKE '246 266%')
	or (HomePhone not LIKE '246 267%')	
	or (HomePhone not LIKE '246 268%')
	or (HomePhone not LIKE '246 269%')
	or (HomePhone not LIKE '246 820%')
	or (HomePhone not LIKE '246 821%')
	or (HomePhone not LIKE '246 822%')
	or (HomePhone not LIKE '246 823%')
	or (HomePhone not LIKE '246 824%')
	or (HomePhone not LIKE '246 825%')
	or (HomePhone not LIKE '246 826%')					
	or (HomePhone not LIKE '246 827%')
	or (HomePhone not LIKE '246 828%')
	or (HomePhone not LIKE '246 829%')

	or (HomePhone not LIKE '256%')
	or (HomePhone not LIKE '257%')
	or (HomePhone not LIKE '258%')
	or (HomePhone not LIKE '259%')
	or (HomePhone not LIKE '260%')
	or (HomePhone not LIKE '261%')
	or (HomePhone not LIKE '262%')
	or (HomePhone not LIKE '263%')
	or (HomePhone not LIKE '264%')
	or (HomePhone not LIKE '265%')
	or (HomePhone not LIKE '266%')
	or (HomePhone not LIKE '267%')	
	or (HomePhone not LIKE '268%')
	or (HomePhone not LIKE '269%')
	or (HomePhone not LIKE '820%')
	or (HomePhone not LIKE '821%')
	or (HomePhone not LIKE '822%')
	or (HomePhone not LIKE '823%')
	or (HomePhone not LIKE '824%')
	or (HomePhone not LIKE '825%')
	or (HomePhone not LIKE '826%')					
	or (HomePhone not LIKE '827%')
	or (HomePhone not LIKE '828%')
	or (HomePhone not LIKE '829%')
	

	or (HomePhone not LIKE '1 246 256%')
	or (HomePhone not LIKE '1 246 257%')
	or (HomePhone not LIKE '1 246 258%')
	or (HomePhone not LIKE '1 246 259%')
	or (HomePhone not LIKE '1 246 260%')
	or (HomePhone not LIKE '1 246 261%')
	or (HomePhone not LIKE '1 246 262%')
	or (HomePhone not LIKE '1 246 263%')
	or (HomePhone not LIKE '1 246 264%')
	or (HomePhone not LIKE '1 246 265%')
	or (HomePhone not LIKE '1 246 266%')
	or (HomePhone not LIKE '1 246 267%')	
	or (HomePhone not LIKE '1 246 268%')
	or (HomePhone not LIKE '1 246 269%')
	or (HomePhone not LIKE '1 246 820%')
	or (HomePhone not LIKE '1 246 821%')
	or (HomePhone not LIKE '1 246 822%')
	or (HomePhone not LIKE '1 246 823%')
	or (HomePhone not LIKE '1 246 824%')
	or (HomePhone not LIKE '1 246 825%')
	or (HomePhone not LIKE '1 246 826%')					
	or (HomePhone not LIKE '1 246 827%')
	or (HomePhone not LIKE '1 246 828%')
	or (HomePhone not LIKE '1 246 829%')

	
	or (HomePhone not LIKE '1-246-256%')
	or (HomePhone not LIKE '1-246-257%')
	or (HomePhone not LIKE '1-246-258%')
	or (HomePhone not LIKE '1-246-259%')
	or (HomePhone not LIKE '1-246-260%')
	or (HomePhone not LIKE '1-246-261%')
	or (HomePhone not LIKE '1-246-262%')
	or (HomePhone not LIKE '1-246-263%')
	or (HomePhone not LIKE '1-246-264%')
	or (HomePhone not LIKE '1-246-265%')
	or (HomePhone not LIKE '1-246-266%')
	or (HomePhone not LIKE '1-246-267%')	
	or (HomePhone not LIKE '1-246-268%')
	or (HomePhone not LIKE '1-246-269%')
	or (HomePhone not LIKE '1-246-820%')
	or (HomePhone not LIKE '1-246-821%')
	or (HomePhone not LIKE '1-246-822%')
	or (HomePhone not LIKE '1-246-823%')
	or (HomePhone not LIKE '1-246-824%')
	or (HomePhone not LIKE '1-246-825%')
	or (HomePhone not LIKE '1-246-826%')					
	or (HomePhone not LIKE '1-246-827%')
	or (HomePhone not LIKE '1-246-828%')
	or (HomePhone not LIKE '1-246-829%')
	
	or (HomePhone not LIKE '1-246256%')
	or (HomePhone not LIKE '1-246257%')
	or (HomePhone not LIKE '1-246258%')
	or (HomePhone not LIKE '1-246259%')
	or (HomePhone not LIKE '1-246260%')
	or (HomePhone not LIKE '1-246261%')
	or (HomePhone not LIKE '1-246262%')
	or (HomePhone not LIKE '1-246263%')
	or (HomePhone not LIKE '1-246264%')
	or (HomePhone not LIKE '1-246265%')
	or (HomePhone not LIKE '1-246266%')
	or (HomePhone not LIKE '1-246267%')	
	or (HomePhone not LIKE '1-246268%')
	or (HomePhone not LIKE '1-246269%')
	or (HomePhone not LIKE '1-246820%')
	or (HomePhone not LIKE '1-246821%')
	or (HomePhone not LIKE '1-246822%')
	or (HomePhone not LIKE '1-246823%')
	or (HomePhone not LIKE '1-246824%')
	or (HomePhone not LIKE '1-246825%')
	or (HomePhone not LIKE '1-246826%')					
	or (HomePhone not LIKE '1-246827%')
	or (HomePhone not LIKE '1-246828%')
	or (HomePhone not LIKE '1-246829%')
	
	or (HomePhone not LIKE '1246256%')
	or (HomePhone not LIKE '1246257%')
	or (HomePhone not LIKE '1246258%')
	or (HomePhone not LIKE '1246259%')
	or (HomePhone not LIKE '1246260%')
	or (HomePhone not LIKE '1246261%')
	or (HomePhone not LIKE '1246262%')
	or (HomePhone not LIKE '1246263%')
	or (HomePhone not LIKE '1246264%')
	or (HomePhone not LIKE '1246265%')
	or (HomePhone not LIKE '1246266%')
	or (HomePhone not LIKE '1246267%')	
	or (HomePhone not LIKE '1246268%')
	or (HomePhone not LIKE '1246269%')
	or (HomePhone not LIKE '1246820%')
	or (HomePhone not LIKE '1246821%')
	or (HomePhone not LIKE '1246822%')
	or (HomePhone not LIKE '1246823%')
	or (HomePhone not LIKE '1246824%')
	or (HomePhone not LIKE '1246825%')
	or (HomePhone not LIKE '1246826%')					
	or (HomePhone not LIKE '1246827%')
	or (HomePhone not LIKE '1246828%')
	or (HomePhone not LIKE '1246829%')
				
	or (HomePhone not LIKE '246-256%')
	or (HomePhone not LIKE '246-257%')
	or (HomePhone not LIKE '246-258%')
	or (HomePhone not LIKE '246-259%')
	or (HomePhone not LIKE '246-260%')
	or (HomePhone not LIKE '246-261%')
	or (HomePhone not LIKE '246-262%')
	or (HomePhone not LIKE '246-263%')
	or (HomePhone not LIKE '246-264%')
	or (HomePhone not LIKE '246-265%')
	or (HomePhone not LIKE '246-266%')
	or (HomePhone not LIKE '246-267%')	
	or (HomePhone not LIKE '246-268%')
	or (HomePhone not LIKE '246-269%')
	or (HomePhone not LIKE '246-820%')
	or (HomePhone not LIKE '246-821%')
	or (HomePhone not LIKE '246-822%')
	or (HomePhone not LIKE '246-823%')
	or (HomePhone not LIKE '246-824%')
	or (HomePhone not LIKE '246-825%')
	or (HomePhone not LIKE '246-826%')					
	or (HomePhone not LIKE '246-827%')
	or (HomePhone not LIKE '246-828%')
	or (HomePhone not LIKE '246-829%')
				
	or (HomePhone not LIKE '(246)256%')
	or (HomePhone not LIKE '(246)257%')
	or (HomePhone not LIKE '(246)258%')
	or (HomePhone not LIKE '(246)259%')
	or (HomePhone not LIKE '(246)260%')
	or (HomePhone not LIKE '(246)261%')
	or (HomePhone not LIKE '(246)262%')
	or (HomePhone not LIKE '(246)263%')
	or (HomePhone not LIKE '(246)264%')
	or (HomePhone not LIKE '(246)265%')
	or (HomePhone not LIKE '(246)266%')
	or (HomePhone not LIKE '(246)267%')	
	or (HomePhone not LIKE '(246)268%')
	or (HomePhone not LIKE '(246)269%')
	or (HomePhone not LIKE '(246)820%')
	or (HomePhone not LIKE '(246)821%')
	or (HomePhone not LIKE '(246)822%')
	or (HomePhone not LIKE '(246)823%')
	or (HomePhone not LIKE '(246)824%')
	or (HomePhone not LIKE '(246)825%')
	or (HomePhone not LIKE '(246)826%')					
	or (HomePhone not LIKE '(246)827%')
	or (HomePhone not LIKE '(246)828%')
	or (HomePhone not LIKE '(246)829%')
				


	or (WorkPhone not LIKE '246256%')
	or (WorkPhone not LIKE '246257%')
	or (WorkPhone not LIKE '246258%')
	or (WorkPhone not LIKE '246259%')
	or (WorkPhone not LIKE '246260%')
	or (WorkPhone not LIKE '246261%')
	or (WorkPhone not LIKE '246262%')
	or (WorkPhone not LIKE '246263%')
	or (WorkPhone not LIKE '246264%')
	or (WorkPhone not LIKE '246265%')
	or (WorkPhone not LIKE '246266%')
	or (WorkPhone not LIKE '246267%')	
	or (WorkPhone not LIKE '246268%')
	or (WorkPhone not LIKE '246269%')
	or (WorkPhone not LIKE '246820%')
	or (WorkPhone not LIKE '246821%')
	or (WorkPhone not LIKE '246822%')	
	or (WorkPhone not LIKE '246823%')
	or (WorkPhone not LIKE '246824%')
	or (WorkPhone not LIKE '246825%')
	or (WorkPhone not LIKE '246826%')
	or (WorkPhone not LIKE '246827%')
	or (WorkPhone not LIKE '246828%')
	or (WorkPhone not LIKE '246829%')
				
	or (WorkPhone not LIKE '246 256%')
	or (WorkPhone not LIKE '246 257%')
	or (WorkPhone not LIKE '246 258%')
	or (WorkPhone not LIKE '246 259%')
	or (WorkPhone not LIKE '246 260%')
	or (WorkPhone not LIKE '246 261%')
	or (WorkPhone not LIKE '246 262%')
	or (WorkPhone not LIKE '246 263%')
	or (WorkPhone not LIKE '246 264%')
	or (WorkPhone not LIKE '246 265%')
	or (WorkPhone not LIKE '246 266%')
	or (WorkPhone not LIKE '246 267%')	
	or (WorkPhone not LIKE '246 268%')
	or (WorkPhone not LIKE '246 269%')
	or (WorkPhone not LIKE '246 820%')
	or (WorkPhone not LIKE '246 821%')
	or (WorkPhone not LIKE '246 822%')
	or (WorkPhone not LIKE '246 823%')
	or (WorkPhone not LIKE '246 824%')
	or (WorkPhone not LIKE '246 825%')
	or (WorkPhone not LIKE '246 826%')					
	or (WorkPhone not LIKE '246 827%')
	or (WorkPhone not LIKE '246 828%')
	or (WorkPhone not LIKE '246 829%')

	or (WorkPhone not LIKE '256%')
	or (WorkPhone not LIKE '257%')
	or (WorkPhone not LIKE '258%')
	or (WorkPhone not LIKE '259%')
	or (WorkPhone not LIKE '260%')
	or (WorkPhone not LIKE '261%')
	or (WorkPhone not LIKE '262%')
	or (WorkPhone not LIKE '263%')
	or (WorkPhone not LIKE '264%')
	or (WorkPhone not LIKE '265%')
	or (WorkPhone not LIKE '266%')
	or (WorkPhone not LIKE '267%')	
	or (WorkPhone not LIKE '268%')
	or (WorkPhone not LIKE '269%')
	or (WorkPhone not LIKE '820%')
	or (WorkPhone not LIKE '821%')
	or (WorkPhone not LIKE '822%')
	or (WorkPhone not LIKE '823%')
	or (WorkPhone not LIKE '824%')
	or (WorkPhone not LIKE '825%')
	or (WorkPhone not LIKE '826%')					
	or (WorkPhone not LIKE '827%')
	or (WorkPhone not LIKE '828%')
	or (WorkPhone not LIKE '829%')
	

	or (WorkPhone not LIKE '1 246 256%')
	or (WorkPhone not LIKE '1 246 257%')
	or (WorkPhone not LIKE '1 246 258%')
	or (WorkPhone not LIKE '1 246 259%')
	or (WorkPhone not LIKE '1 246 260%')
	or (WorkPhone not LIKE '1 246 261%')
	or (WorkPhone not LIKE '1 246 262%')
	or (WorkPhone not LIKE '1 246 263%')
	or (WorkPhone not LIKE '1 246 264%')
	or (WorkPhone not LIKE '1 246 265%')
	or (WorkPhone not LIKE '1 246 266%')
	or (WorkPhone not LIKE '1 246 267%')	
	or (WorkPhone not LIKE '1 246 268%')
	or (WorkPhone not LIKE '1 246 269%')
	or (WorkPhone not LIKE '1 246 820%')
	or (WorkPhone not LIKE '1 246 821%')
	or (WorkPhone not LIKE '1 246 822%')
	or (WorkPhone not LIKE '1 246 823%')
	or (WorkPhone not LIKE '1 246 824%')
	or (WorkPhone not LIKE '1 246 825%')
	or (WorkPhone not LIKE '1 246 826%')					
	or (WorkPhone not LIKE '1 246 827%')
	or (WorkPhone not LIKE '1 246 828%')
	or (WorkPhone not LIKE '1 246 829%')

	
	or (WorkPhone not LIKE '1-246-256%')
	or (WorkPhone not LIKE '1-246-257%')
	or (WorkPhone not LIKE '1-246-258%')
	or (WorkPhone not LIKE '1-246-259%')
	or (WorkPhone not LIKE '1-246-260%')
	or (WorkPhone not LIKE '1-246-261%')
	or (WorkPhone not LIKE '1-246-262%')
	or (WorkPhone not LIKE '1-246-263%')
	or (WorkPhone not LIKE '1-246-264%')
	or (WorkPhone not LIKE '1-246-265%')
	or (WorkPhone not LIKE '1-246-266%')
	or (WorkPhone not LIKE '1-246-267%')	
	or (WorkPhone not LIKE '1-246-268%')
	or (WorkPhone not LIKE '1-246-269%')
	or (WorkPhone not LIKE '1-246-820%')
	or (WorkPhone not LIKE '1-246-821%')
	or (WorkPhone not LIKE '1-246-822%')
	or (WorkPhone not LIKE '1-246-823%')
	or (WorkPhone not LIKE '1-246-824%')
	or (WorkPhone not LIKE '1-246-825%')
	or (WorkPhone not LIKE '1-246-826%')					
	or (WorkPhone not LIKE '1-246-827%')
	or (WorkPhone not LIKE '1-246-828%')
	or (WorkPhone not LIKE '1-246-829%')
	
	or (WorkPhone not LIKE '1-246256%')
	or (WorkPhone not LIKE '1-246257%')
	or (WorkPhone not LIKE '1-246258%')
	or (WorkPhone not LIKE '1-246259%')
	or (WorkPhone not LIKE '1-246260%')
	or (WorkPhone not LIKE '1-246261%')
	or (WorkPhone not LIKE '1-246262%')
	or (WorkPhone not LIKE '1-246263%')
	or (WorkPhone not LIKE '1-246264%')
	or (WorkPhone not LIKE '1-246265%')
	or (WorkPhone not LIKE '1-246266%')
	or (WorkPhone not LIKE '1-246267%')	
	or (WorkPhone not LIKE '1-246268%')
	or (WorkPhone not LIKE '1-246269%')
	or (WorkPhone not LIKE '1-246820%')
	or (WorkPhone not LIKE '1-246821%')
	or (WorkPhone not LIKE '1-246822%')
	or (WorkPhone not LIKE '1-246823%')
	or (WorkPhone not LIKE '1-246824%')
	or (WorkPhone not LIKE '1-246825%')
	or (WorkPhone not LIKE '1-246826%')					
	or (WorkPhone not LIKE '1-246827%')
	or (WorkPhone not LIKE '1-246828%')
	or (WorkPhone not LIKE '1-246829%')
	
	or (WorkPhone not LIKE '1246256%')
	or (WorkPhone not LIKE '1246257%')
	or (WorkPhone not LIKE '1246258%')
	or (WorkPhone not LIKE '1246259%')
	or (WorkPhone not LIKE '1246260%')
	or (WorkPhone not LIKE '1246261%')
	or (WorkPhone not LIKE '1246262%')
	or (WorkPhone not LIKE '1246263%')
	or (WorkPhone not LIKE '1246264%')
	or (WorkPhone not LIKE '1246265%')
	or (WorkPhone not LIKE '1246266%')
	or (WorkPhone not LIKE '1246267%')	
	or (WorkPhone not LIKE '1246268%')
	or (WorkPhone not LIKE '1246269%')
	or (WorkPhone not LIKE '1246820%')
	or (WorkPhone not LIKE '1246821%')
	or (WorkPhone not LIKE '1246822%')
	or (WorkPhone not LIKE '1246823%')
	or (WorkPhone not LIKE '1246824%')
	or (WorkPhone not LIKE '1246825%')
	or (WorkPhone not LIKE '1246826%')					
	or (WorkPhone not LIKE '1246827%')
	or (WorkPhone not LIKE '1246828%')
	or (WorkPhone not LIKE '1246829%')
				
	or (WorkPhone not LIKE '246-256%')
	or (WorkPhone not LIKE '246-257%')
	or (WorkPhone not LIKE '246-258%')
	or (WorkPhone not LIKE '246-259%')
	or (WorkPhone not LIKE '246-260%')
	or (WorkPhone not LIKE '246-261%')
	or (WorkPhone not LIKE '246-262%')
	or (WorkPhone not LIKE '246-263%')
	or (WorkPhone not LIKE '246-264%')
	or (WorkPhone not LIKE '246-265%')
	or (WorkPhone not LIKE '246-266%')
	or (WorkPhone not LIKE '246-267%')	
	or (WorkPhone not LIKE '246-268%')
	or (WorkPhone not LIKE '246-269%')
	or (WorkPhone not LIKE '246-820%')
	or (WorkPhone not LIKE '246-821%')
	or (WorkPhone not LIKE '246-822%')
	or (WorkPhone not LIKE '246-823%')
	or (WorkPhone not LIKE '246-824%')
	or (WorkPhone not LIKE '246-825%')
	or (WorkPhone not LIKE '246-826%')					
	or (WorkPhone not LIKE '246-827%')
	or (WorkPhone not LIKE '246-828%')
	or (WorkPhone not LIKE '246-829%')
				
	or (WorkPhone not LIKE '(246)256%')
	or (WorkPhone not LIKE '(246)257%')
	or (WorkPhone not LIKE '(246)258%')
	or (WorkPhone not LIKE '(246)259%')
	or (WorkPhone not LIKE '(246)260%')
	or (WorkPhone not LIKE '(246)261%')
	or (WorkPhone not LIKE '(246)262%')
	or (WorkPhone not LIKE '(246)263%')
	or (WorkPhone not LIKE '(246)264%')
	or (WorkPhone not LIKE '(246)265%')
	or (WorkPhone not LIKE '(246)266%')
	or (WorkPhone not LIKE '(246)267%')	
	or (WorkPhone not LIKE '(246)268%')
	or (WorkPhone not LIKE '(246)269%')
	or (WorkPhone not LIKE '(246)820%')
	or (WorkPhone not LIKE '(246)821%')
	or (WorkPhone not LIKE '(246)822%')
	or (WorkPhone not LIKE '(246)823%')
	or (WorkPhone not LIKE '(246)824%')
	or (WorkPhone not LIKE '(246)825%')
	or (WorkPhone not LIKE '(246)826%')					
	or (WorkPhone not LIKE '(246)827%')
	or (WorkPhone not LIKE '(246)828%')
	or (WorkPhone not LIKE '(246)829%')
	or (CellPhone is null)	
	)
	
	and TransactionDate >= { ts '2008-05-01 00:00:00.000' } and TransactionDate < { ts '2008-08-01 00:00:00.000' }	
	
	
group by  c.CARDNUMBER
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3 
	,HomePhone
	,CellPhone
	,WorkPhone
	,CardClass
	,c.ClassCode
	,CycleCode
	,Email
	,Creditcard

order by  c.CARDNUMBER

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-08-25 : 14:28:14
I think your ORs should be ANDs.


Be One with the Optimizer
TG
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2008-08-25 : 14:28:33
Use "and" instead of "or".
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-08-25 : 14:31:42
and we got the idea after the first several hundred prefixes - not really necessary to post all 3 billion of them

Be One with the Optimizer
TG
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2008-08-25 : 14:40:24
Also try something like this:

where CellPhone not like '%246%25[6-9]%'
and CellPhone not like '%246%26[0-9]%' and CellPhone not like '%246%82[0-9]%'
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts
Posted - 2008-08-25 : 14:48:12
quote:
Originally posted by Van

Use "and" instead of "or".


Ok I will try this and see what happens
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site