I currently have a Staff table and a related StaffHolidays table.
In the StaffHolidays table I can add a record with a start date and an end date.
I also have a PublicHolidays table so it can account for them as well. This all works reasonably well and I can work out how many work days a member of staff has taken.
However, I now want to be able to select a date range, and it tell me the holidays taken for each member of staff between those dates,
So, I want to calculate the amount of days holiday within the date range.
in this example it would be 5 days.
Hopefully that makes sense, and somebody has done something like this before, or know a better way of managing this.
Thanks in advance!