Question:
it is necessary to get the date periods. input data:Answer:
This is a gaps and islands problem. One trick we can use here is to create a pseudo-group which tracks to which island of continuous dates each record belongs.Demo
In the first CTE above, the alias
cnt
gets assigned to a value of 1 whenever the preceding date is not one day earlier than the current date. We can then sum over this column and obtain groups of dates forming a continuous range.If you have better answer, please add a comment about this, thank you!