I’m trying to this in this SQL, planning to chuck it into a view and plug it into PowerBI - but if it’s possible in Power Query then that’s fine! Couldn’t see a SQL section.
I’m trying to calculate instances and date ranges of sickness. My data is running downwards like this:
Sickness Dates Example.xlsx (12.9 KB)
It needs to take into account weekends/bank holidays though. So for example, with these example dates the results should be like this, with 11/6/21 - 14/6/21 being counted as one instance as it spans a weekend:
I have a SQL table with all the dates in, each marked up whether it’s a working day or not. My plan was to take the rolling year from this of the working days, index them from earliest to latest. Join this to my sickness dates, so each sickness day now has an ID as to their position in the rolling year of working days. Then planned to loop somehow to check if the next row is a sequential ID and group them all together with the minumum/maximum dates of each instance. I’m a bit rusty on recursive CTE loops in SQL so can’t seem to crack it.
Here’s my dates table to help:
Rolling Dates Table.xlsx (16.7 KB)
Hope explained that well enough and appreciate any help, as said fine in SQL or Power Query!