I was thinking about the date diff of 28 days that Lowfatspread helped me with on friday and of course I have come up with one minor problem which will give a small number of students but the output will be incorrect
I have attached a sample student data to show you what I mean
When testing for 28 days (4 weeks) I haven't taken into account that the college will break for christmas, easter and so on, and the way this college works is the week number will continue on each week of the holidays. Looking at the example student on spreadsheet I will try to explain
When I run the search of >=28 days on the example student I get the rows highlighted in blue out. Looking at the dates there is a date diff of > 28 days but the students has only been off 3 weeks. You will notice that week_no 18+ 19 are not there this represents christmas (the reason why they are not included in my table is because I only take each week that the course is active for). The query was to find students who have been off for 4 weeks or more, its a way of checking students who have breached over 4 weeks absence and I don't want this student being contacted because really they haven't missed four weeks of college so they could complain.
When checking absences > 28days I need to check that each week_no is sequential ie 17, 18, 19 to ensure that the students has missed > 28 days. For each week number is missing I need to add 7 days to the datediff of 28 days
so in ref to the example student because week 18 + 19 are missed then I need to add 14 days to the 28 days and then check for 42 days diff.
I really hope this makes sense
Hope you can help
thanks in advance