CODEWITHSUNDEEP
sqlsql-server-2008
Standage
Standage

Reputation: 1517

SQL, Search by Date and not exists

I have two tables and need to search for all entries that exist in one table in another table by idProduct, only if the date (dateStamp) is less than or older than 7 days.

Because the api I'm using is restricted to only processing 3000 results at a time, the application will close and the next time I run the application I only want the idProducts that are say 3000 or greater for that idProduct, this will be run numerous times for the Suppliercode wll most likely already exist in the table.

So I've been looking at the not exists and getdate functions in sql but not been able to get the desired results.

SELECT
   *
FROM
   products
WHERE
   (active = - 1)
   AND suppliercode = 'TIT'
   and (NOT EXISTS
     (SELECT
         idProduct
      FROM compare
      WHERE
         (products.idProduct = idProduct)
         OR (compare.dateStamp < DATEADD(DAY,-7,GETDATE()))))

Any pointers would be great, I've changed the OR to AND but it doesn't seem to bring back the correct results.

Upvotes: 0

Views: 2251

Answers (3)

Kyra
Kyra

Reputation: 5407

I am guessing you want to match the rows in the two tables by idProduct as right now your inner query (NOT EXISTS (SELECT idProduct FROM compare WHERE (products.idProduct = idProduct) OR (compare.dateStamp < DATEADD(DAY,-7,GETDATE())))) looks like it is finding all rows that don't match. As your subquery finds all rows that match or where the date is older than 7 days and makes sure that they don't exist.

Is this what your want? 

SELECT * 
FROM products as p
LEFT JOIN compare as c
    ON p.idProduct = c.idProduct
WHERE p.active = -1 and p.suppliercode = 'TIT' and c.dateStamp < DATEADD(DAY,-7,GETDATE())

Upvotes: 1

Eddie Paz
Eddie Paz

Reputation: 2241

Try NOT IN instead:

...
and ProductId NOT IN 
     (SELECT 
         idProduct 
      FROM compare 
      WHERE 
         (products.idProduct = idProduct) 
         OR (compare.dateStamp < DATEADD(DAY,-7,GETDATE())))) 
....

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25347

Have you tried this one yet?

SELECT * FROM products 
WHERE (active = - 1) AND 
suppliercode = 'TIT'   
and ipProduct NOT IN 
(
   SELECT idProduct FROM compare 
   WHERE 
   (products.idProduct = idProduct) OR 
   (compare.dateStamp < DATEADD(DAY,-7,GETDATE()))
)

Upvotes: 1

Related Questions