Is there any way to Find Missing Date Ranges in SQL?
Basically, I'm trying to find out how I can identify any missing days the last 3 months.
I've already got a list of dates and volumes but unable to find a way to identify missing dates.
I want to put it in an automation workflow, so when there are any dates missing I can use an SFMC validation activity to stop the automation when there are any missing dates
I think your best bet would be to create a DE to house all the days of the year (one date for each row). This can have things get tricky depending on leap year and handling around the new year, but it really is the simplest way to check this.
You would then write a query that runs a left join of your date DE to your volumes DE to pull only those that don't exist in your volumes DE.
sample:
SELECT d.Date
FROM dateDE d
LEFT JOIN volumeDE v
ON d.Date = v.Date
WHERE v.Date IS NULL
AND d.Date >= DATEADD(MONTH, -3, GETDATE())
This will only return dates that are in the last 3 months and do not exist in your volume DE. I would recommend running this with the 'OVERWRITE' Action, to ensure it clears on every use.
You can then set a validation interaction in Automation Studio to see if this DE is empty or not, which will then let you know if any day was missed in the past 3 months.
