This post has already been read 278 times!

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
	(
	  [UserName] varchar(5)
	, [UserDate] DATETIME2
	, [UserCode] int
)
	
INSERT INTO UserTable
	(
	   [UserName]
	, [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
(	SELECT	UserName,
			UserDate,
			UserCode,
			GroupingSet = DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserDate), UserDate)
	FROM	UserTable
)
SELECT	UserName,
		StartDate = MIN(UserDate),
		EndDate = MAX(UserDate),
		Result = COUNT(NULLIF(UserCode, 0))
FROM	CTE
GROUP BY UserName, GroupingSet
HAVING COUNT(NULLIF(UserCode, 0)) > 1
ORDER BY UserName, StartDate

Leave a Reply

Post Navigation