Reputation: 5614
I'm trying to pull off all the NEW numbers that have called from a database in the last 4 weeks who haven't ever called before.
So basically any new numbers in the last 4 weeks....
The script I have made so far is:-
SELECT Telephone,
Houseno,
Street,
Tostreet,
Passengername,
Acceptancedate
FROM Telephone AS t
WHERE AcceptanceDate >= DATEADD(day,-28, GETDATE()) AND Telephone like '07%' or Telephone like '+447%' or Telephone like '+44 7%'
or Telephone like '+44 7%' or Telephone like '+47%' or Telephone like '01%'
AND NOT EXISTS
( SELECT 1
FROM Telephone AS t2
WHERE t2.Telephone = t.Telephone
AND t2.Acceptancedate < DATEADD(day,-28, GETDATE())
);
However the client is saying it is returning too many numbers which leads me to think the above script is incorrect some how,
Does anybody have any idea what I am doing wrong?
Upvotes: 1
Views: 74
Reputation: 16137
Take a look into what Operator Precendence means for your boolean expression in the WHERE clause. It's a bit like multiplication versus addition precedence in arithmetic expressions, with operator AND having similar precendence to multiplication and operator OR having similar precendence to addition.
Your WHERE expression evaluates as follows:
(AcceptanceDate >= DATEADD(day,-28, GETDATE()) AND Telephone like '07%')
OR
(Telephone like '+447%')
...
OR
(Telephone like '01%' AND NOT EXISTS
( SELECT 1
FROM Telephone AS t2
WHERE t2.Telephone = t.Telephone
AND t2.Acceptancedate < DATEADD(day,-28, GETDATE())
);
)
This is probably not what you meant?
Upvotes: 0
Reputation: 3266
I'd rephrase this a bit:
WHERE AcceptanceDate >= DATEADD(day,-28, GETDATE()) AND (Telephone like '07%' or Telephone like '+447%' or Telephone like '+44 7%'
or Telephone like '+44 7%' or Telephone like '+47%' or Telephone like '01%')
AND NOT EXISTS
( SELECT Telephone
FROM Telephone AS t2
WHERE t2.Telephone = t.Telephone
AND t2.Acceptancedate < DATEADD(day,-28, GETDATE())
);
Could you please give it a go?
I have included the OR parts in brackets, so they are evaluated in one go, otherwise it could take it as
AcceptanceDate >= DATEADD(day,-28, GETDATE()) AND Telephone like '07%'
OR
Telephone like '+447%'
OR
...
OR
Telephone like '01%'
AND NOT EXISTS
( SELECT Telephone
FROM Telephone AS t2
WHERE t2.Telephone = t.Telephone
AND t2.Acceptancedate < DATEADD(day,-28, GETDATE())
);
If you take a look at the above, for example when it looks for "Telephone like +447" then it ignores the rest of the WHERE clause.
Hope this will help you, and that it makes sense :)
Upvotes: 1