Happy new year all 🙂

Giving the original OP the benefit of the doubt, it looks like either the load strategy has been changed or different sources are employing different strategies.  It is clear from the sample data that this is abstracted from the real problem.  @ Jeff, if the SCD data is looking at continuous days attendance then a gap of one day would be perfectly acceptable and would definitely generate a new SCD record - I have used this pattern myself when tracking data quality issues to identify failure rate and mean-time-to-fix. I think the OP has the right approach to fixing the issue which is to explode out the records with the contiguous days, then find islands and gaps using a calendar table and collapse the records back down.  There may be more elegant ways to solve the problem but if it is a one-off data realignment I would not be looking to spend days on code to optimise the solution.  Do what is pragmatic and move on.