CODEWITHSUNDEEP
sqlsql-servert-sql
thatuxguy
thatuxguy

Reputation: 2528

Get records which were not updated the day before

I need to get a list of items which were not updated the day before.

I currently have...

 SELECT     dbo.aboProducts.asin, dbo.aboProducts.sku
 FROM       dbo.aboProducts INNER JOIN
            dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin
 WHERE     (dbo.LowestPrices.priceDate <= DATEADD(day, - 1, GETDATE()))

This however returns nothing. If i change the <= to >= i get results from the last 24 hours. I just need to return any items which were NOT updated, and leave the ones that were updated alone.

Upvotes: 0

Views: 90

Answers (3)

AnandPhadke
AnandPhadke

Reputation: 13506

Try this:

 SELECT     dbo.aboProducts.asin, dbo.aboProducts.sku
 FROM       dbo.aboProducts INNER JOIN
            dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin
 WHERE     (dbo.LowestPrices.priceDate != DATEADD(day, - 1, GETDATE()))

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24116

try this:

SELECT     aboProducts.asin, aboProducts.sku
FROM       dbo.aboProducts 
where aboProducts.asin 
not in(
     select aboProducts.asin 
     FROM       dbo.aboProducts 
     INNER JOIN
                dbo.LowestPrices 
     ON aboProducts.asin = LowestPrices.productAsin
     WHERE     (LowestPrices.priceDate >= DATEADD(day, - 1, GETDATE()))
 )

Upvotes: 2

podiluska
podiluska

Reputation: 51514

That looks fine, assuming every Product has at least one entry in LowestPrices. Do you actually have any items that weren't updated?

Upvotes: 2

Related Questions