Sharepoint CAML Query to avoid Calendar overlap booking

I am using Calendar List for some room bookings everything is fine but only struggling with Overlapp or double booking of a room on the same day and time.

I am trying to figure out by passing th开发者_JAVA技巧e data entered by the user in the form to CAML Query and check whether this entry is existing or not

If the entry is already existing Cancel Booking or if not proceed.

For example: if From: 10:00 AM To 11:00 AM is already booked.

the overlapping scenarios may be:

if user enters in the form From: 10:00 AM To 10:30 AM

From: 9:00 AM To 11:00 AM.. I am assuming The date is same and only time matters for me.

How to get the CAML query if the date is same and only time varies and check the user input From & To time with in the range exists in already Booked items.

using query something below but not validating for all scenarios

<Where><And><Geq><FieldRef Name='EventDate' /><Value IncludeTimeValue='TRUE'  Type='DateTime'>" + strSPEventDateFormat + "</Value></Geq><And><Leq><FieldRef Name='EndDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + strSPEndDateFormat + "</Value></Leq><Eq><FieldRef Name='Room' /><Value Type='Lookup'>" + strCheckRoomAvail + "</Value></Eq></And></And></Where>

Please help me on this

Thanks in advance

Write CAML to get all rooms conflicting with the START Time. Start time must not be between start and end times selected by user AND write CAML to get all rooms conflicting with the END Time. END time must not be between start and end times selected by user

If the room selected by user is not in the above results returned by CAML then you can go ahead and book the room.

 q.Query = "<Where><And><Geq><FieldRef Name=""StartTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(startTime) + _
        "</Value></Geq><Leq><FieldRef Name=""StartTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(endTime) + _
        "</Value></Leq></And></Where><OrderBy><FieldRef Name=""ID"" Ascending=""True"" /></OrderBy>"

q1.Query = "<Where><And><Geq><FieldRef Name=""EndTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(startTime) + _
        "</Value></Geq><Leq><FieldRef Name=""EndTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(endTime) + _
        "</Value></Leq></And></Where><OrderBy><FieldRef Name=""ID"" Ascending=""True"" /></OrderBy>"

This thread is quite old, but i just came across the same problem. So here is my working solution, may it help some one.

Note: For time relatet search-caml you need the IncludeTimeValue='TRUE' parameter of the

/**
 * {Boolean} formModeEdit To determine if this form is an New or Edit form
 */
function checkDoubleBooking(formModeEdit, listId, currentItemId, startDate, endDate) {
  var results = [];
  var liHtml = "";
  var checkOverlappingQueryArray = [];

  //Construct caml query
  checkOverlappingQueryArray.push("<And>");
  checkOverlappingQueryArray.push("<Leq>");
  checkOverlappingQueryArray.push("<FieldRef Name='EventDate' /><Value Type='DateTime' IncludeTimeValue='TRUE'>" + moment(endDate).format() + "</Value>");
  checkOverlappingQueryArray.push("</Leq>");
  checkOverlappingQueryArray.push("<Geq>");
  checkOverlappingQueryArray.push("<FieldRef Name='EndDate' /><Value Type='DateTime' IncludeTimeValue='TRUE'>" + moment(startDate).format() + "</Value>");
  checkOverlappingQueryArray.push("</Geq>");
  checkOverlappingQueryArray.push("</And>");
  if (formModeEdit) {
    checkOverlappingQueryArray.unshift("<And>");
    checkOverlappingQueryArray.push("<Neq>");
    checkOverlappingQueryArray.push("<FieldRef Name='ID' /><Value Type='Integer'>" + currentItemId + "</Value>");
    checkOverlappingQueryArray.push("</Neq>");
    checkOverlappingQueryArray.push("</And>");
  }
  checkOverlappingQueryArray.unshift("<Where>");
  checkOverlappingQueryArray.push("</Where>");
  checkOverlappingQueryArray.unshift("<Query>");
  checkOverlappingQueryArray.push("</Query>");

  jQuery().SPServices({
      operation: "GetListItems",
      async: false,
      listName: listId,
      CAMLViewFields: "<ViewFields>" +
        "<FieldRef Name='ID' />" +
        "<FieldRef Name='Title' />" +
        "<FieldRef Name='EventDate' />" +
        "<FieldRef Name='EndDate' />" +
        "<FieldRef Name='PeoplePickerField1' />" +
        "</ViewFields>",
      CAMLQuery: checkOverlappingQuery,
      CAMLQueryOptions: "<QueryOptions>" +
        "<ExpandUserField>True</ExpandUserField>" + //Expand People Picker values
        "</QueryOptions>",
      CAMLRowLimit: 10, // Override the default view rowlimit
      completefunc: function(xData, Status) {
        $(xData.responseXML).SPFilterNode("z:row").each(function() {
            countOverlappingEvents++;
            results.push({
              title: $(this).attr("ows_Title"),
              eventDate: $(this).attr("ows_EventDate"),
              endDate: $(this).attr("ows_EndDate"),
              peoplePickerField1: userToJsonObject($(this).attr("ows_PeoplePickerField1"))
            });
          }
        }
      });
    return results;
  }

  function userToJsonObject(userObj) {
    if (userObj.length === 0) {
      return null;
    } else {
      var thisUser = userObj.split(";#");
      var thisUserExpanded = thisUser[1].split(",#")
      if (thisUserExpanded.length == 1) {
        return {
          userId: thisUser[0],
          userName: thisUser[1]
        }
      } else {
        return {
          userId: thisUser[0],
          userName: thisUserExpanded[0].replace(/(,,)/g, ","),
          loginName: thisUserExpanded[1].replace(/(,,)/g, ","),
          email: thisUserExpanded[2].replace(/(,,)/g, ","),
          sipAddress: thisUserExpanded[3].replace(/(,,)/g, ","),
          title: thisUserExpanded[4].replace(/(,,)/g, ",")
        }
      }
    }
  }

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.2/moment-with-locales.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices-2014.02.min.js"></script>