HOW TO FIND COMPLETE OVERLAP FOR BLACKOUT SCHEDULES

[surya h]

var plannedStart = '2023-06-01 20:00:00';
var plannedEnd = '2023-06-10 20:00:00';

var newstart = new GlideDateTime(plannedStart);
var newend = new GlideDateTime(plannedEnd);

var blackoutSchedule = new GlideRecord('cmn_schedule_span');
blackoutSchedule.addQuery('schedule', 'a445656545654kjhkjhjhgjhghg8');
blackoutSchedule.query();

var isPartialOverlap = false;
var isCompleteOverlap = false;

while (blackoutSchedule.next()) {
var recurrence = blackoutSchedule.schedule;
var schedule = new GlideSchedule(recurrence);

if (schedule.isInSchedule(newstart) && schedule.isInSchedule(newend)) {
isCompleteOverlap = true;
break;
} else if (schedule.isInSchedule(newstart) || schedule.isInSchedule(newend)) {
isPartialOverlap = true;
}
}

if (isCompleteOverlap) {
gs.info("Complete overlap with the schedule");
} else if (isPartialOverlap) {
gs.info("Partial overlap with the schedule");
} else {
gs.info("No overlap with the schedule");
}

For partial overlap it is working as expected, but for complete overlap it is not working, it shows that the planned start and planned end are not in schedules window

for example: 

PARTIAL OVERLAP:

plannedStart = '2023-05-30 20:00:00';
plannedEnd = '2023-06-03 20:00:00';

is working when my schedule entry start from 2023-06-02 20:00:00 to 2023-06-04 20:00:00

plannedStart = '2023-06-03 20:00:00';
plannedEnd = '2023-06-10 20:00:00';

is working when my schedule entry start from 2023-06-02 20:00:00 to 2023-06-04 20:00:00

COMPLETE OVERLAP:

plannedStart = '2023-06-01 20:00:00';
plannedEnd = '2023-06-10 20:00:00';

is not working when even my schedule entry start from 2023-06-02 20:00:00 to 2023-06-04 20:00:00

kindly provide me a work around for this.


Thanks in advance.

[Bert_c1]

Hi surya H,

I don't see any problem with your script, except you break out of the while loop for isCompleteOverlap=true, but not for isPartialOverlap = true. So you continue to process cmn_schedule_span records.

I don't have a cmn_schedule with sys_id= a445656545654kjhkjhjhgjhghg8 to test. So I can't query my cmn_schedule_span table for records there. Also, no record in cmn_schedule_span has a Start and End value in my instance. So hard to reproduce what you see. I can test after I add like data in my instance. 

[Bert_c1]

Hi @surya h ,

I did set up a test, where I have a schedule with a schedule entry that has Start date time = 2023-06-02 20:00:00 and End date time = 2023-06-04 20:00:00. and I ran your script (with some debug) in Scripts Background and got what you found.  What does work as expected is when the start and end fall within the schedule entry. Values that start before and after the schedule entry result in the output: ""No overlap with the schedule".  Script follows:

// partial (works)
//var plannedStart = '2023-05-30 20:00:00';
//var plannedEnd = '2023-06-03 20:00:00';
// partial (works)
//var plannedStart = '2023-06-03 20:00:00';
//var plannedEnd = '2023-06-10 20:00:00';
// complete (No overlap is returned)
//var plannedStart = '2023-06-01 20:00:00';
//var plannedEnd = '2023-06-10 20:00:00';
// complete 2 (Complete is returned)
var plannedStart = '2023-06-03 00:00:00';
var plannedEnd = '2023-06-04 18:00:00';

var newstart = new GlideDateTime(plannedStart);
var newend = new GlideDateTime(plannedEnd);

var blackoutSchedule = new GlideRecord('cmn_schedule_span');
blackoutSchedule.addQuery('schedule', '21c43ab1970ba1101dd3fa67f053af73');
blackoutSchedule.query();
gs.info( "Found " + blackoutSchedule.getRowCount() + " cmn_schedule_span records to process.");
gs.info("newStart = " + newstart + ", newend = " + newend);

var isPartialOverlap = false;
var isCompleteOverlap = false;

while (blackoutSchedule.next()) {
	var recurrence = blackoutSchedule.schedule;
	gs.info("Checking recurrence = " + recurrence);
	var schedule = new GlideSchedule(recurrence);

	if (schedule.isInSchedule(newstart) && schedule.isInSchedule(newend)) {
		isCompleteOverlap = true;
		break;
	} else if (schedule.isInSchedule(newstart) || schedule.isInSchedule(newend)) {
		isPartialOverlap = true;
	}
}

if (isCompleteOverlap) {
	gs.info("Complete overlap with the schedule");
} else if (isPartialOverlap) {
	gs.info("Partial overlap with the schedule");
} else {
	gs.info("No overlap with the schedule");
}

/*
*** Script: Found 1 cmn_schedule_span records to process.
*** Script: newStart = 2023-06-03 00:00:00, newend = 2023-06-04 18:00:00
*** Script: Checking recurrence = 21c43ab1970ba1101dd3fa67f053af73
*** Script: Complete overlap with the schedule
*/

This seems to be expected behavior as the API for isInSchedule() states the return value as:

Flag that indicates whether the specified date and time is within the schedule.

Valid values:

• true: Date and time is within the schedule.
• false: Date and time are outside of the schedule.

API document is here:

https://developer.servicenow.com/dev.do#!/reference/api/utah/server/no-namespace/c_GlideScheduleScopedAPI#r_ScopedGlideScheduleIsInSchedule_GlideDateTime?navFilter=isinschedule 

You will need a different algorithm if you want to see when a date-time range encompasses a schedule entry.

[Markus Kraus]

@surya h If you can live with just the output, that the give start- and end-date are within a given schedule, you can use the following function:

function isInSchedule(schedule, startDate, endDate) {
  return schedule.duration(
    new GlideDateTime(startDate),
    new GlideDateTime(endDate)
  ).getNumericValue() > 0;
}

var tests = [
  { start_date: '2023-05-30 20:00:00', end_date: '2023-06-03 20:00:00' },
  { start_date: '2023-06-03 20:00:00', end_date: '2023-06-10 20:00:00' },
  { start_date: '2023-06-01 20:00:00', end_date: '2023-06-10 20:00:00' },
  { start_date: '2023-06-02 20:00:00', end_date: '2023-06-04 20:00:00' },
  { start_date: '2023-07-01 20:00:00', end_date: '2023-07-10 20:00:00' }
];

var scheduleSysID = '2dc31b8297476110424f3931f053af2e'; // use your own scheulde SysID here
var sched = new GlideSchedule(scheduleSysID);
tests.forEach(function (dateInterval) {
  gs.info("testing date " + dateInterval.start_date + " -> " + dateInterval.end_date
    + "\nisInSchedule: " + isInSchedule(sched, dateInterval.start_date, dateInterval.end_date)
    + "\n");
});

output:

*** Script: Testing Schedule Span - from 2023-06-02 20:00:00 to 2023-06-06 20:00:00
*** Script: testing date 2023-05-30 20:00:00 -> 2023-06-03 20:00:00
isInSchedule: partial

*** Script: testing date 2023-06-03 20:00:00 -> 2023-06-10 20:00:00
isInSchedule: partial

*** Script: testing date 2023-06-01 20:00:00 -> 2023-06-10 20:00:00
isInSchedule: complete

*** Script: testing date 2023-06-02 20:00:00 -> 2023-06-04 20:00:00
isInSchedule: partial

*** Script: testing date 2023-07-01 20:00:00 -> 2023-07-10 20:00:00
isInSchedule: none

Note: You cannot determine if a time interval is fully covered by a schedule (at least not without iterating over every schedule entry), because you would need the know the begin and end of the schedule.

If you still require to a overlap-type analysis, you can use this function:

function overLapType(scheduleSpanStartDate, scheduleSpanEndDate, startDate, endDate) {
  var range1 = [scheduleSpanStartDate, scheduleSpanEndDate];
  var range2 = [startDate, endDate];
  if ((range1[1] < range2[0]) || (range1[0] > range2[1])) {
    return 'none';
  }

  var overlap = [
    range1[0] <= range2[0] ? range2[0] : range1[0],
    range1[1] <= range2[1] ? range1[1] : range2[1]
  ];

  if (overlap[0] <= scheduleSpanStartDate && overlap[1] >= scheduleSpanEndDate) {
    return 'complete';
  }

  return 'partial';
}

var tests = [
  { start_date: '2023-05-30 20:00:00', end_date: '2023-06-03 20:00:00' },
  { start_date: '2023-06-03 20:00:00', end_date: '2023-06-10 20:00:00' },
  { start_date: '2023-06-01 20:00:00', end_date: '2023-06-10 20:00:00' },
  { start_date: '2023-06-02 20:00:00', end_date: '2023-06-04 20:00:00' },
  { start_date: '2023-07-01 20:00:00', end_date: '2023-07-10 20:00:00' }
];

var scheduleSysID = '2dc31b8297476110424f3931f053af2e';
var scheduleSpanGr = new GlideRecord('cmn_schedule_span');
scheduleSpanGr.addQuery('schedule', scheduleSysID);
scheduleSpanGr.query();
while (scheduleSpanGr.next()) {
  var scheduleSpanStart = new GlideDateTime(scheduleSpanGr.start_date_time.getDisplayValue());
  var scheduleSpanEnd = new GlideDateTime(scheduleSpanGr.end_date_time.getDisplayValue());
  gs.info("Testing Schedule Span - from " + scheduleSpanStart + " to " + scheduleSpanEnd);
  tests.forEach(function (dateInterval) {
    gs.info("testing date " + dateInterval.start_date + " -> " + dateInterval.end_date
      + "\nisInSchedule: " + overLapType(scheduleSpanStart, scheduleSpanEnd, dateInterval.start_date, dateInterval.end_date)
      + "\n");
  });
}

output:

*** Script: Testing Schedule Span - from 2023-06-02 20:00:00 to 2023-06-06 20:00:00
*** Script: testing date 2023-05-30 20:00:00 -> 2023-06-03 20:00:00
isInSchedule: partial

*** Script: testing date 2023-06-03 20:00:00 -> 2023-06-10 20:00:00
isInSchedule: partial

*** Script: testing date 2023-06-01 20:00:00 -> 2023-06-10 20:00:00
isInSchedule: partial

*** Script: testing date 2023-06-02 20:00:00 -> 2023-06-04 20:00:00
isInSchedule: complete

*** Script: testing date 2023-07-01 20:00:00 -> 2023-07-10 20:00:00
isInSchedule: none

[Bert_c1]

Hi,

Marcus's examples shows working logic. I tested the following using what you have started with to include the case when the planned start and end dates include the schedule entry. See:

// partial (works)
//var plannedStart = '2023-05-30 20:00:00';
//var plannedEnd = '2023-06-03 20:00:00';
// partial (works)
//var plannedStart = '2023-06-03 20:00:00';
//var plannedEnd = '2023-06-10 20:00:00';
// complete (No overlap is returned)
var plannedStart = '2023-06-01 20:00:00';
var plannedEnd = '2023-06-10 20:00:00';
// complete 2 (Complete is returned)
//var plannedStart = '2023-06-03 00:00:00';
//var plannedEnd = '2023-06-04 18:00:00';

var newstart = new GlideDateTime(plannedStart);
var newend = new GlideDateTime(plannedEnd);

var blackoutSchedule = new GlideRecord('cmn_schedule_span');
blackoutSchedule.addQuery('schedule', '21c43ab1970ba1101dd3fa67f053af73');
blackoutSchedule.query();
gs.info( "Found " + blackoutSchedule.getRowCount() + " cmn_schedule_span records to process.");
gs.info("Checking planned newStart = " + newstart + ", planned newend = " + newend);

var isPartialOverlap = false;
var isCompleteOverlap = false;
var isCompleteSpan = false;

while (blackoutSchedule.next()) {
	var recurrence = blackoutSchedule.schedule;
//	gs.info("Checking recurrence = " + recurrence);
	var schedule = new GlideSchedule(recurrence);

	if (schedule.isInSchedule(newstart) && schedule.isInSchedule(newend)) {
		isCompleteOverlap = true;
		break;
	} else if (schedule.isInSchedule(newstart) || schedule.isInSchedule(newend)) {
		isPartialOverlap = true;
	}

	// Now check the planned dates to see if they include the schedule entry
	var sStart = blackoutSchedule.start_date_time;
	var sEnd = blackoutSchedule.end_date_time;
//	gs.info("Checking schedule start = " + sStart + ", schedule end = " + sEnd);
	var ssdtStr = sStart.substr(0,4) + "-" + sStart.substr(4,2) + "-" + sStart.substr(6,2) + " " + sStart.substr(9,2) + ":" + sStart.substr(11,2) + ":" + sStart.substr(13,2);
	var sedtStr = sEnd.substr(0,4) + "-" + sEnd.substr(4,2) + "-" + sEnd.substr(6,2) + " " + sEnd.substr(9,2) + ":" + sEnd.substr(11,2) + ":" + sEnd.substr(13,2);
//	gs.info("Checking schedule start = " + ssdtStr + ", schedule end = " + sedtStr);
	var sDate = new GlideDateTime(ssdtStr);
	var eDate = new GlideDateTime(sedtStr);
	gs.info("Checking schedule start = " + sDate + ", schedule end = " + eDate);
	if ((newstart <= sDate) && (newend >= eDate)) {
		isCompleteSpan = true;
	}
}

if (isCompleteOverlap) {
	gs.info("Planned dates complete overlap with the schedule");
} else if (isPartialOverlap) {
	gs.info("Planned dates partial overlap with the schedule");
} else {
	gs.info("Planned dates have no overlap with the schedule");
}
if (isCompleteSpan) {
	gs.info("Planned dates include the schedule");
}

/*
*** Script: Found 1 cmn_schedule_span records to process.
*** Script: Checking planned newStart = 2023-06-01 20:00:00, planned newend = 2023-06-10 20:00:00
*** Script: Checking schedule start = 2023-06-02 20:00:00, schedule end = 2023-06-04 20:00:00
*** Script: Planned dates have no overlap with the schedule
*** Script: Planned dates include the schedule
*/

You can remove or comment out the debug lines. Or just add the new lines from above at the end of your while loop.