Forum Discussion

ChrisGreen's avatar
ChrisGreen
Qrew Trainee
6 years ago

Find gaps in dates

I have an issue where we book out our consultants using dates and want to find the gaps/soonest availability. Each booking is either a day or half day. The half day is just done as checkbox and brings out the value 1 or 0.5 accordingly and booking 1.5 days is possible.

I do currently have a calendar colour coded to show this, but would love some kind of report to say something along the lines of:
  • In the next 3 months, Consultant A is next available on the dd/mm/yy for 5 days
  • In the next 3 months, Consultant B is not available
  • In the next 3 months, Consultant C is next available on the dd/mm/yy for 0.5 days 
Even better would be if there is a way we could say that 3 days are required, it would read:
  • In the next 3 months, Consultant A is next available on the dd/mm/yy
  • In the next 3 months, Consultant B is not available
  • In the next 3 months, Consultant C is next available on the dd/mm/yy (+24 days for example to the above as the 0.5 days above would not suffice so would find the next gap in their diary) 
In essence the dream solution would look at the days required and just say the soonest time each consultant could do it. We have normal working weeks Mon-Fri, and would like it to run using these days alone.

Many thanks
Forms
reports and charts
Tables and fields

1 Reply

Sort By
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    6 years ago
    Needless to say this can easily be done with script. However you have to refine a few areas of your requirements:

    (1) Using a checkbox to indicate a half day is just complicating the matter. Would the checkbox alone indicate the morning or afternoon assignment? You need to break your assignments into blocks of time that are fully defined by start and end field values that are unambiguous.

    (2) The duration of the assignment should not be a field but rather a parameter of a query.  I should be input by the user into some type of dialog box prior running the query (similar in concept to _ask_the_user)

    (3) Let's say you have fields and in a child table indicating the start and end time for a consultant's assignments. Let s(n) and e(n) be the values of and for each of the n child records. All you rally have to do is run through each of the following intervals and check if the interval is greater than or equal to the duration required for the assignment:

    is s(1) - Now() => duration?
    is s(2) - e(1) => duration?
    is s(3) - e(2)  => duration?
    is s(4) - e(3)  => duration?
    ...
    is s(n) - e(n-1) => duration

    This can easily be done in script.