This is perhaps the most often asked question and at the same time possibly having the lowest correct answers vs questions ratio. See a collection of these questions (and their answers) in FAQ: Open & Close Dates.
I usually recommend method 1 (periods touch calendar periods) in The Cross Join Collection although it seems you want method 2 (periods touch individual calendar dates) from the same collection. I have attached a workbook with both methods.
Custom SQL Method 1
SELECT * FROM [IP running$] d, [Ref period$] l WHERE d.[Start Date] <= l.[Ref period end] and d.[End Date] >= l.[Ref period start]
Custom SQL Method 2
SELECT * FROM [IP running$] d, [Ref dates$] l WHERE l.[Ref date] BETWEEN d.[Start Date] and d.[End Date]
If you have open periods (end date is null) you will need to substitute it with a constant or calculated end date.
we need to open Excel files with the legacy connector to be able to create custom SQLs:
Here are some ideas which ask for improvements in this regard which you might like to support with your vote:
That's fantastic, many thanks! I tried method 2 and it works perfectly.
Indeed, I had seen some of the previous questions but never found such a clear answer.
I will certainly look into supporting the improvements.
Thanks again and all the best,
You are welcome Usually when I share these methods, they are ignored Therefore it feels nice that they are immediately understood and appreciated. You made my day