Press "Enter" to skip to content

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data:

This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set which happens to be my missing question days. Only, I have added an extra output in the second query to let me know the day of the week that the missing question occurred on. Maybe I forgot to enter it because it was a weekend day or maybe I opted to not create one at all because the day lands on a Holiday. Let’s take a small peek at the results.

This is a good use for tally tables (or for a calendar table, which is basically a date dimension called something else so you can feel comfortable dropping in a non-warehouse system).