Consecutive days in date range
Hiya There are a multitude of posts on this and I've got some way with the SQLBI video on youtube but keep getting stuck with my specific issue. I need to report on sickness within the business, both longterm (lts) and short term (sts). To classify Sickness I use the following logic: LTS = Sick for 28 or more consecutive days AND sick on the last date in the period. STS = Sick for less than 28 days Further, if someone starts their period of absence in a previous period, but do not meet the LTS critera for the previous period, (but the absence is part of the same consecutive string) then the days in the previous period should be marked as STS and the days in the period where it is triggered should be marked as LTS. AND Once someone is LTS they should remain as LTS, for example if I trigger the LTS condition in the current period but return before the end of the next then those days should also be LTS not STS even though the absence has ended before the end of the period. For tagging each day I would ideally like this as columns as opposed to a measure, both for how many consecutive days and the LTS/STS. I will want to do further work with this information which will be easier to deal with if I can see it in the table. **you will note from the calendar table below I report on two seperate periods of data (helpful I know). I'm happy that this is in seperate columns. I will use the Calendar as a filter and use measures for the reporting figures. I hope this makes sense, particularly the LTS/STS conditions but please feel free to ask for further clarification. Also I'm not particularly fussy if this is done in Queery or as a calculated column. Conscious of the potential size of the table as this grows YOY and will need to be mindful of the size. The data will be coming from a SQL Database, as an import. (our in house SQL knowledge is poor so keen that this is done somewhere I can be in control of it) The report table which shows every day of the year for every person in the business (circa. 3000 people - so annually just over a million rows, minimum) The sickness data table is laid out as follows The calendar is The report is linked as follows on the date key
