CODEWITHSUNDEEP
sql-server-2008t-sql
thatuxguy
thatuxguy

Reputation: 2528

List of items which were not updated in the last 24 hours

Morning, This is similar to a question i asked yesterday [question]: SQL query to get latest prices, depending on the date

I need to return a list of ASIN's which are have not been updated within 24 hours from the last update date from the lowest price table. I can get a list of ASIN's, however it returns all of them. Could someone pleas help me with the SQL to get this list please?

SELECT     asin
FROM       dbo.aboProducts
WHERE     (asin NOT IN
               (SELECT    aboProducts_1.asin
                FROM      dbo.aboProducts AS aboProducts_1 INNER JOIN
                          dbo.LowestPrices ON aboProducts_1.asin = dbo.LowestPrices.productAsin
                WHERE    (dbo.aboProducts.amzLive = 'true') AND 
                         (dbo.LowestPrices.priceDate < DATEADD(day, - 1, GETDATE()))))

Upvotes: 0

Views: 78

Answers (2)

tranceporter
tranceporter

Reputation: 2261

@podiluska's answer is correct. Since you are using 

WHERE     (asin NOT IN

you want to get all items which have been updated in the last 24 hours by doing

dbo.LowestPrices.priceDate > DATEADD(day, - 1, GETDATE())

and then exclude them by using the NOT IN clause, which will leave you with all items that have not been updated in the last 24 hours.

Upvotes: 0

podiluska
podiluska

Reputation: 51504

I think you've got your comparison the wrong way round

Try

SELECT     asin 
FROM       dbo.aboProducts 
WHERE     (asin NOT IN 
               (SELECT    aboProducts_1.asin 
                FROM      dbo.aboProducts AS aboProducts_1 INNER JOIN 
                          dbo.LowestPrices ON aboProducts_1.asin = dbo.LowestPrices.productAsin 
                WHERE    (dbo.aboProducts.amzLive = 'true') AND  
                         (dbo.LowestPrices.priceDate > DATEADD(day, - 1, GETDATE()))))

Upvotes: 1

Related Questions