How to select missing date value

Question

I am currently trying to view missing dates if a particular "property" is missing a date value for the past 3 days:

SELECT property, business_date
    FROM my_table
        WHERE business_date BETWEEN DATEADD(DAY, -2, @business_date) AND @business_date
        AND property IN
        (
        SELECT property
            FROM my_table
                WHERE business_date BETWEEN DATEADD(DAY, -2, @business_date) AND @business_date
                    GROUP BY property
                        HAVING COUNT(property) <> 3
    )

It currently shows this:

0088 2017-05-16 00:00:00.000
0088 2017-05-18 00:00:00.000
0094 2017-05-17 00:00:00.000
0094 2017-05-18 00:00:00.000

I am trying to get it to show the missing values, not the values I have. So I would like it to tell me:

0088 2017-05-17 00:00:00.000
0094 2017-05-16 00:00:00.000

I tried creating a calendar table:

DECLARE @calendar TABLE
(
    date DATETIME
)
INSERT INTO @calendar
SELECT DATEADD(DAY, -2, @business_date) UNION ALL
SELECT DATEADD(DAY, -1, @business_date) UNION ALL
SELECT DATEADD(DAY, 0, @business_date)

I should clarify...the dates are not NULL, they are completely missing. — zfemmer, May 19 '17 at 21:07
I did try that and for some reason I couldn't get it to work still.+ — zfemmer, May 19 '17 at 21:14
as scsimon said. http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function — Juan Carlos Oropeza, May 19 '17 at 21:16
Doing that, I am able to pull the date, but not the "property" with it. — zfemmer, May 19 '17 at 21:21

score 1 · Accepted Answer · answered May 19 '17 at 21:24

1

You should cross join distinct property values with dates and left join it to the original table.

SELECT P.property,C.DATE
FROM (SELECT DISTINCT PROPERTY FROM my_table) P
CROSS JOIN @calendar C 
LEFT JOIN MY_TABLE M ON M.PROPERTY=P.PROPERTY AND M.BUSINESS_DATE=C.DATE
WHERE M.BUSINESS_DATE IS NULL

answered May 19 '17 at 21:24

Vamsi Prabhala

48,685
4
36
58

That's perfect! Thank you! – zfemmer May 19 '17 at 21:31

How to select missing date value

1 Answers1