CODEWITHSUNDEEP
sqlsql-server-2008gaps-and-islands
zfemmer
zfemmer

Reputation: 97

How to select missing date value

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)

Upvotes: 1

Views: 535

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 1

Related Questions