0

If the current request is for 04/02/2018 - 04/06/2018, I need to display in a datatable any other requests that include 04/02 - 04/06. For example, the datatable should display the following requests

  • ID 1 - Employee A - 04/02/2018
  • ID 2 - Employee B - 04/04/2018-04/05/2018
  • ID 3 - Employee C - 04/04/2018-04/09/2018

Below code displays ID#1 and 2 in the datatable but not 3 and I can't figure out how to include ID#3.

var dateArray = dateString.split(",");
var stDate = new Date(dateArray[0]);
var enDate = new Date(dateArray[1]);

var checkInfo = webAbsUrl + "/_api/web/Lists/getByTitle('AbsenceRequest')/Items?$\
    filter=((StartDateISO ge datetime'" + stDate.toISOString() + "') and (EndDateISO le datetime'" + enDate.toISOString() + "') and (Section eq '" + strA + "'))&$\
    top=300&$select=Id, Absentee, RequestType, ActivityType, EventDate, EndDate, Status";
2

You have three types of matches:

  • StartDateISO between the two dates
  • EndDateISO between the two dates
  • StartDateISO < stDate AND EndDateISO > enDate

So try this:

(
  (StartDateISO ge datetime'" + stDate.toISOString() + "') and (StartDateISO le datetime'" + enDate.toISOString() + "')
or
  (EndDateISO ge datetime'" + stDate.toISOString() + "') and (EndtDateISO le datetime'" + enDate.toISOString() + "')
or
  (StartDateISO le datetime'" + stDate.toISOString() + "') and (EndDateISO ge datetime'" + enDate.toISOString() + "')
)

Note that this does not deal with either date being blank.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.