Checking for rows relating to previous days which might not exist

Question

I am having some trouble in my check of whether or not I received prices yesterday for let´s say - my apples.

The tricky part is that in the table where prices are stored, there won´t be any row relating to yesterday if I did not get prices yesterday. So how can I make my check everyday if I want to be sure that the day before I got some prices?

You could use a [calendar table](http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql) and join to this so that you have a record for each distinct day, just some will not have any records in your prices table and so will return a `NULL` value which you can `ISNULL()` to some other value. — 3N1GM4, Jan 09 '17 at 13:06

score 1 · Answer 1 · edited May 23 '17 at 12:01

If you have a Calendar table (see here for example) with a field called Date and making some assumptions about your data structure:

SELECT c.[Date], 
       ISNULL(p.Prices,'No Prices')
FROM Calendar c
LEFT JOIN Prices p ON c.[Date] = p.[Date]

Your question is not very clear, but it actually might even be as simple as just checking for the presence of a row for the previous day, rather than reporting across all dates (in this case I consider there are multiple products):

SELECT DISTINCT 
    prod.Product,
    CASE WHEN prev.Product IS NULL 
         THEN 'No Prices for yesterday' 
         ELSE 'Prices recorded for yesterday'
    END AS PricesYesterday
FROM Prices prod
LEFT JOIN Prices prev ON prev.Product = prod.Product
                      AND prev.[Date] = dateadd(day,datediff(day,0,GETDATE()),0) - 1

Checking for rows relating to previous days which might not exist

1 Answers1