Gaps and islands problems involve missing values in a sequence. Solving the gaps problem requires finding the ranges of missing values, whereas solving the islands problem involves finding the ranges of existing values. The sequences of values in gaps and islands problems can be numeric, such as a sequence of order IDs, some of which were deleted. An example of the gaps problem in this case would be finding the ranges of deleted order IDs.

CREATE TABLE FOR GAPS & ISLANDS
``````CREATE TABLE UserTable
(
, [UserDate] DATETIME2
, [UserCode] int
)

INSERT INTO UserTable
(
, [UserDate]
, [UserCode])
VALUES
('user1', '09-01-2014', 232),
('user1', '09-02-2014', 0),
('user1', '09-03-2014', 121),
('user1', '09-08-2014', 122),
('user1', '09-09-2014', 0),
('user1', '09-10-2014', 144),
('user1', '09-11-2014', 166),
('user2', '09-01-2014', 177),
('user2', '09-04-2014', 188),
('user2', '09-05-2014', 199),
('user2', '09-06-2014', 0),
('user2', '09-07-2014', 155)``````
SOLUTION FOR GAPS & ISLANDS
``````WITH CTE AS
UserDate,
UserCode,
FROM	UserTable
)
StartDate = MIN(UserDate),
EndDate = MAX(UserDate),
Result = COUNT(NULLIF(UserCode, 0))
FROM	CTE
HAVING COUNT(NULLIF(UserCode, 0)) > 1