I have a set of data that shows value per person claimed over a range of days. Sometimes these have been entered one line of data per day and sometimes they have been entered as a date range of a week or more.
I need to identify where someone has made a claim for more than five consecutive working days but I am struggling to figure out how to identify this.
I've attached a simplified example of the type of information we have and am hoping that someone might be able to help? I did think about trying to expand the data so that we had a flag for each day to say whether there was a claim made or not, but I couldn't work out how to get from the date ranges (start and end date) to a full list of dates in that range.
Any ideas guys?
So I think I've managed to get pretty much what I need as a combination of ideas.
The problem I had is that each person may have several claims and the data is a mix of people who have submitted a date range and others that have submitted one claim per day.
This might not end up being the final version I use, however it is a huge step closer. many thanks fo everyone for sending their ideas!