0

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

  • What triggers the search for missing dates? can you give more context and/or the code you've already got? – Robs Jun 26 '18 at 19:33
  • Well, I have a table which has a list of emails sent over a 3 month period. but within that 3 month period, but I don't have any emails sent on any given day. example of dates 25/06/2018,23/06/2018,22/06/2018 so you can see I don't have any email sent on the 24th. I was going to use a case statement to identify if I have any false day where this not populated and it's not working. here's the SQL code: – Peter Ho Jun 26 '18 at 19:53
  • What is the structure of the table? what exactly are you doing to stop doing when you find a missing date? can you give more background? have you read this How to Ask – Robs Jun 26 '18 at 19:55
  • cant add the sql its too long – Peter Ho Jun 26 '18 at 19:58
  • the data in the prepopulated list is Eventdate and Total ( total volume of each day) – Peter Ho Jun 26 '18 at 19:59
1

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.

| improve this answer | |
  • Thanks, do you know if there’s an easy way in mc to create a DE that contains all the days of the year?? – Peter Ho Jun 27 '18 at 15:58
  • Easiest is to create an excel doc and use the fill feature then import as CSV into a DE: support.office.com/en-us/article/… – Gortonington Jun 27 '18 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.