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
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” ...