nsilva
nsilva

Reputation: 5614

SQL Server 2008 - Select where NOT EXSIST

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

Answers (2)

TT.
TT.

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

Alex Szabo
Alex Szabo

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

Related Questions