SQL Cookbook

Chapter 10. Working with Ranges

This chapter is about “everyday” queries that involve ranges. Ranges are common in everyday life. For example, projects that we work on range over consecutive periods of time. In SQL, it’s often necessary to search for ranges, or to generate ranges, or to otherwise manipulate range-based data. The queries you’ll read about here are slightly more involved than the queries found in the preceding chapters, but they are just as common, and they’ll begin to give you a sense of what SQL can really do for you when you learn to take full advantage of it.

10.1. Locating a Range of Consecutive Values

Problem

You want to determine which rows represent a range of consecutive projects. Consider the following result set from view V, which contains data about a project and its start and end dates:

	select *
	  from V

	PROJ_ID PROJ_START  PROJ_END
	------- ----------- -----------
	      1 01-JAN-2005 02-JAN-2005
	      2 02-JAN-2005 03-JAN-2005
	      3 03-JAN-2005 04-JAN-2005
	      4 04-JAN-2005 05-JAN-2005
	      5 06-JAN-2005 07-JAN-2005
	      6 16-JAN-2005 17-JAN-2005
	      7 17-JAN-2005 18-JAN-2005
	      8 18-JAN-2005 19-JAN-2005
	      9 19-JAN-2005 20-JAN-2005
	     10 21-JAN-2005 22-JAN-2005
	     11 26-JAN-2005 27-JAN-2005
	     12 27-JAN-2005 28-JAN-2005
	     13 28-JAN-2005 29-JAN-2005
	     14 29-JAN-2005 30-JAN-2005

Excluding the first row, each row’s PROJ_START should equal the PROJ_END of the row before it (“before” is defined as PROJ_ID–1 for the current row). Examining the first five rows from view V, PROJ_IDs 1 through 3 are part of the same “group” ...