Gap Analysis Using Algebra


Introduction

This article shows how to solve a gaps and islands problem using simple algebra. No SQL Server loops or cursors are required for this solution.

Problem

Scheduling constraints occasionally require the presence of some “feature” over all time intervals of fixed width. A typical example might be the following rule for rostering flight attendants: Rosters of one month’s duration must have at least 24 consecutive hours free of duty among any 7 consecutive days

Dividing the month into 720 hours, a roster may be represented by a sequence of 720 binary digits (0s and 1s), where 0 represents rest and 1