Skip to main content
Software Engineering

Return to Answer

added 37 characters in body
Source Link
InformedA
InformedA
  • 3k
  • 2
  • 21
  • 30

I would like to offer you my solution for this by recognizing that:

  1. All appointments, single or re-occurring, have at least a date
  2. Under reasonable settings, re-occurring appointments often have a pattern

In my solution, you will have for each appointment a start date and an end date. In the case of single appointment, you can leave the end date the same as the start date. In the case of re-occurring appointment into the future, you can leave the end date as empty or any far into the future value.

Now for all appointments, you have an addition column called: type and pattern. In this column, you can have an enum kind of values specifying the repetition pattern such as daily, weekly, monthly, bi-weekly. For appointments that are not re-occurring, the type can be one-time.

This also deals with the case where an appointment is actually part of a re-occurring one, but was re-scheduled to be out of pattern. You set the type of it into something such as re-schedule can possibly have an additional reference column to refer to the original re-occurring appointment.

Now when you want to search for all appointments (of any types) to match your date range, you can use the start and end date together with the 'type and pattern' column. A specific example for the case of search for appointments in the range of Oct 1st, 2014 to Dec 1st, 2014 is as follows:

select * from APP as A where
    ((A.sd >= $start AND A.sd <= $end) OR (A.ed <= $end AND A.ed >= $start) OR (A.sd <= $start AND A.ed >= $end) ) 
    AND (A.type = 'Single' OR A.type = 'ReSchedule' OR inRange($start, $end, A.sd, A.type) )

Here you have to build your own function for inRange(), the semantic is taking the $end variable as an integer, minus the integer for A.sd and divide by the pattern interval. Then you check if $end minus the division's mod is greater or equal to $start

InRange() will slow down your query by looking at all raw matches. But this will be much better than your original design concept. You can either implement InRange() by using PL/SQL or write an external method using the SQL query's results to properly filter the raw matches.

I would like to offer you my solution for this by recognizing that:

  1. All appointments, single or re-occurring, have at least a date
  2. Under reasonable settings, re-occurring appointments often have a pattern

In my solution, you will have for each appointment a start date and an end date. In the case of single appointment, you can leave the end date the same as the start date. In the case of re-occurring appointment into the future, you can leave the end date as empty or any far into the future value.

Now for all appointments, you have an addition column called: type and pattern. In this column, you can have an enum kind of values specifying the repetition pattern such as daily, weekly, monthly, bi-weekly. For appointments that are not re-occurring, the type can be one-time.

This also deals with the case where an appointment is actually part of a re-occurring one, but was re-scheduled to be out of pattern. You set the type of it into something such as re-schedule can possibly have an additional reference column to refer to the original re-occurring appointment.

Now when you want to search for all appointments (of any types) to match your date range, you can use the start and end date together with the 'type and pattern' column. A specific example for the case of search for appointments in the range of Oct 1st, 2014 to Dec 1st, 2014 is as follows:

select * from APP as A where
    ((A.sd >= $start AND A.sd <= $end) OR (A.ed <= $end AND A.ed >= $start) ) 
    AND (A.type = 'Single' OR A.type = 'ReSchedule' OR inRange($start, $end, A.sd, A.type) )

Here you have to build your own function for inRange(), the semantic is taking the $end variable as an integer, minus the integer for A.sd and divide by the pattern interval. Then you check if $end minus the division's mod is greater or equal to $start

InRange() will slow down your query by looking at all raw matches. But this will be much better than your original design concept. You can either implement InRange() by using PL/SQL or write an external method using the SQL query's results to properly filter the raw matches.

I would like to offer you my solution for this by recognizing that:

  1. All appointments, single or re-occurring, have at least a date
  2. Under reasonable settings, re-occurring appointments often have a pattern

In my solution, you will have for each appointment a start date and an end date. In the case of single appointment, you can leave the end date the same as the start date. In the case of re-occurring appointment into the future, you can leave the end date as empty or any far into the future value.

Now for all appointments, you have an addition column called: type and pattern. In this column, you can have an enum kind of values specifying the repetition pattern such as daily, weekly, monthly, bi-weekly. For appointments that are not re-occurring, the type can be one-time.

This also deals with the case where an appointment is actually part of a re-occurring one, but was re-scheduled to be out of pattern. You set the type of it into something such as re-schedule can possibly have an additional reference column to refer to the original re-occurring appointment.

Now when you want to search for all appointments (of any types) to match your date range, you can use the start and end date together with the 'type and pattern' column. A specific example for the case of search for appointments in the range of Oct 1st, 2014 to Dec 1st, 2014 is as follows:

select * from APP as A where
    ((A.sd >= $start AND A.sd <= $end) OR (A.ed <= $end AND A.ed >= $start) OR (A.sd <= $start AND A.ed >= $end) ) 
    AND (A.type = 'Single' OR A.type = 'ReSchedule' OR inRange($start, $end, A.sd, A.type) )

Here you have to build your own function for inRange(), the semantic is taking the $end variable as an integer, minus the integer for A.sd and divide by the pattern interval. Then you check if $end minus the division's mod is greater or equal to $start

InRange() will slow down your query by looking at all raw matches. But this will be much better than your original design concept. You can either implement InRange() by using PL/SQL or write an external method using the SQL query's results to properly filter the raw matches.

Source Link
InformedA
InformedA
  • 3k
  • 2
  • 21
  • 30

I would like to offer you my solution for this by recognizing that:

  1. All appointments, single or re-occurring, have at least a date
  2. Under reasonable settings, re-occurring appointments often have a pattern

In my solution, you will have for each appointment a start date and an end date. In the case of single appointment, you can leave the end date the same as the start date. In the case of re-occurring appointment into the future, you can leave the end date as empty or any far into the future value.

Now for all appointments, you have an addition column called: type and pattern. In this column, you can have an enum kind of values specifying the repetition pattern such as daily, weekly, monthly, bi-weekly. For appointments that are not re-occurring, the type can be one-time.

This also deals with the case where an appointment is actually part of a re-occurring one, but was re-scheduled to be out of pattern. You set the type of it into something such as re-schedule can possibly have an additional reference column to refer to the original re-occurring appointment.

Now when you want to search for all appointments (of any types) to match your date range, you can use the start and end date together with the 'type and pattern' column. A specific example for the case of search for appointments in the range of Oct 1st, 2014 to Dec 1st, 2014 is as follows:

select * from APP as A where
    ((A.sd >= $start AND A.sd <= $end) OR (A.ed <= $end AND A.ed >= $start) ) 
    AND (A.type = 'Single' OR A.type = 'ReSchedule' OR inRange($start, $end, A.sd, A.type) )

Here you have to build your own function for inRange(), the semantic is taking the $end variable as an integer, minus the integer for A.sd and divide by the pattern interval. Then you check if $end minus the division's mod is greater or equal to $start

InRange() will slow down your query by looking at all raw matches. But this will be much better than your original design concept. You can either implement InRange() by using PL/SQL or write an external method using the SQL query's results to properly filter the raw matches.