> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/10.-working-with-ranges.md).

# 10. Working with Ranges

## Locating a Range of Consecutive Values

We want to determine which rows represent a range of consecutive projects.

For example, consider the below data. Excluding the first row, each row’s PROJ\_START should equal the PROJ\_END of the row before it. We want to find the range of dates for consecutive projects, we would like to return all rows where the current PROJ\_END equals the next row’s PROJ\_START

```
select * from V

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

Sample Output

```
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2020 02-JAN-2020
2 02-JAN-2020 03-JAN-2020
3 03-JAN-2020 04-JAN-2020
6 16-JAN-2020 17-JAN-2020
7 17-JAN-2020 18-JAN-2020
8 18-JAN-2020 19-JAN-2020
11 26-JAN-2020 27-JAN-2020
12 27-JAN-2020 28-JAN-2020
13 28-JAN-2020 29-JAN-2020
```

```
select proj_id, proj_start, proj_end
from (
    select proj_id, proj_start, proj_end,
    lead(proj_start)over(order by proj_id) next_proj_start
    from V
) alias
where next_proj_start = proj_end
```

## Finding Differences Between Rows in the Same Group or Partition

We want to return the DEPTNO, ENAME, and SAL of each employee along with the difference in SAL between employees in the same department. The difference should be between each current employee and the employee hired immediately afterward. For each employee hired last in his department, return “N/A” for the difference.

<figure><img src="https://3632859567-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FPd6ktrA5pPLsZJktj2fm%2Fuploads%2Fgit-blob-cef7a8b1532e37aa92a4c95fa9321ab02ef4afa6%2Fimage.png?alt=media" alt="" width="352"><figcaption></figcaption></figure>

```
with next_sal_tab (deptno,ename,sal,hiredate,next_sal)
as
(
  select 
  deptno, 
  ename, 
  sal, 
  hiredate,
  lead(sal)over(partition by deptno order by hiredate) as next_sal 
  from emp 
)
select deptno, ename, sal, hiredate, coalesce(cast(sal-next_sal as char), 'N/A') as diff
from next_sal_tab
```

## Locating the Beginning and End of a Range of Consecutive Values

We have located the ranges of consecutive values and want to find just their start and end points.

**Sample data and output**

<figure><img src="https://3632859567-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FPd6ktrA5pPLsZJktj2fm%2Fuploads%2Fgit-blob-9701595df2c0f27883870266b327d30f77184c2b%2Fimage.png?alt=media" alt="" width="563"><figcaption></figcaption></figure>

```
SELECT
    proj_grp,
    MIN(proj_start),
    MAX(proj_end)
FROM
    (
        SELECT
            proj_id,
            proj_start,
            proj_end,
            SUM(flag) OVER( ORDER BY  proj_id ) proj_grp
        FROM
            (
                SELECT
                    proj_id,
                    proj_start,
                    proj_end,
                    CASE
                        WHEN LAG(proj_end) OVER(  ORDER BY proj_id  ) = proj_start THEN
                            0
                        ELSE
                            1
                    END flag
                FROM
                    v
            ) alias1
    ) alias2
GROUP BY
    proj_grp
```

## Filling in Missing Values in a Range of Values

We want to return the number of employees hired each year for the entire decade of the 2005s (2000-2009), but there are some years in which no employees were hired.

**Sample Output**

<figure><img src="https://3632859567-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FPd6ktrA5pPLsZJktj2fm%2Fuploads%2Fgit-blob-d54e1a316e7926eeb2fff85296791d3b5ade1b92%2Fimage.png?alt=media" alt="" width="116"><figcaption></figcaption></figure>

```
SELECT
    x.yr,
    coalesce(cnt, 0) cnt
FROM
    (
        SELECT
            EXTRACT(YEAR FROM MIN(hiredate) OVER()) - mod(EXTRACT(YEAR FROM MIN(hiredate) OVER()), 10) + ROWNUM - 1 yr
        FROM
            emp
        WHERE
            ROWNUM <= 10
    ) x
    LEFT JOIN (
        SELECT
            TO_NUMBER(to_char(hiredate, 'YYYY')) yr,
            COUNT(*)                             cnt
        FROM
            emp
        GROUP BY
            TO_NUMBER(to_char(hiredate, 'YYYY'))
    ) y ON ( x.yr = y.yr )
```

## Generating Consecutive Numeric Values

We need to have row source generator like below.

<figure><img src="https://3632859567-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FPd6ktrA5pPLsZJktj2fm%2Fuploads%2Fgit-blob-4d08f2fea8f9af1f60a28c17659db543d7d64ef1%2Fimage.png?alt=media" alt="" width="88"><figcaption></figcaption></figure>

```
SELECT
    array id
FROM
    dual
MODEL
    DIMENSION BY ( 0 idx )
    MEASURES ( 1 array )
    RULES ITERATE(10) ( array[iteration_number]= iteration_number + 1 )
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/10.-working-with-ranges.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.