## Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

### Expected Result:

```Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933```

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

### 14 thoughts on “Puzzle of the Week #18: Find available employee numbers”

1. Zohar Elkayam July 27, 2016 / 3:31 pm

Oracle 12c solution:

```drop table emps purge;

create table emps as
select r
from (select rownum r from dual connect by rownum < 8000)
where r > 7370
and r not in (7499, 7521, 7566, 7654, 7698, 7782, 7788, 7839, 7844, 7876, 7900, 7902)

SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM EMPS
MATCH_RECOGNIZE (
ORDER BY r
MEASURES
A.r firstemp,
LAST(r) lastemp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B*)
DEFINE B AS r = PREV(r)+1
);
```

2. Zohar Elkayam July 27, 2016 / 3:49 pm

Here is a pre-12c solution (there are more elegant solutions, but this works.. :))
also, fixed the creation of the data sample from first answer…

```drop table emps purge;

create table emps as
select r
from (select rownum r from dual connect by rownum < 8000)
where r >= 7370 and r <= 7933
and r not in (7499, 7521, 7566, 7654, 7698, 7782, 7788, 7839, 7844, 7876, 7900, 7902);

SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
from (
select nvl(lag(r) over(order by r), minr) firstemp, q lastemp
from (select r, lag(r) over(order by r) q, min(r) over() minr, max(r) over() maxr from emps e1)
where r != q + 1
union
select q, nvl(lead(r) over(order by r), maxr)
from (select r, lead(r) over(order by r) q, min(r) over() minr, max(r) over() maxr from emps e1)
where r +1 != q);
```
3. Krishna Jamal July 28, 2016 / 4:29 am

WITH
t AS ( SELECT MIN(Empno) AS Empno FROM Emp),
t1 AS ( SELECT Empno+LEVEL-1 Id
FROM t
CONNECT BY LEVEL < (SELECT MAX(Empno)-MIN(Empno)+1 FROM Emp)
MINUS
SELECT Empno FROM Emp )
SELECT MIN(id)||'-'||MAX(id) "Avail. Emp Number"
FROM (SELECT Id, Id-ROWNUM Rng FROM t1)
GROUP BY Rng
ORDER BY 1;

4. Krishna Jamal July 29, 2016 / 3:26 am

WITH t AS
(
SELECT Empno, LEAD(Empno,1) OVER(ORDER BY Empno) EmpID
FROM Emp
)
SELECT Min_id||’ – ‘||Max_id “Avail. EmpNo”
FROM( SELECT Empno+1 Min_id, EmpID-1 Max_id FROM t WHERE Empno+1 EmpID);

• Krishna Jamal July 29, 2016 / 3:31 am

Sorry WHERE didn’t type properly….

WITH t AS
(
SELECT Empno, LEAD(Empno,1) OVER(ORDER BY Empno) EmpID
FROM Emp
)
SELECT Min_id||’ – ‘||Max_id “Avail. EmpNo”
FROM( SELECT Empno+1 Min_id, EmpID-1 Max_id FROM t
WHERE Empno+1 EmpID);

• Krishna Jamal July 29, 2016 / 3:33 am

In WHERE clause I typed “” (not equal) Operator but here it’s not showing…

5. Pawan Kumar Khowal July 29, 2016 / 11:30 am

With highly optimized execution plan & least code

SELECT CONCAT(v1 ,’ – ‘, v2) [Avail. Emp Numbers] FROM
(
SELECT Empno v1 ,LEAD(Empno) OVER(ORDER BY Empno) v2
FROM Emp
)t
WHERE v2 IS NOT NULL

–Pawan Kumar Khowal

• Zohar Elkayam July 29, 2016 / 2:41 pm

that doesn’t give the requested output?

```1	7370 – 7371
2	7371 – 7372
3	7372 – 7373
4	7373 – 7374
5	7374 – 7375
6	7375 – 7376
7	7376 – 7377
8	7377 – 7378
9	7378 – 7379
10	7379 – 7380
11	7380 – 7381
12	7381 – 7382
13	7382 – 7383
[...]
```

```1	7370 - 7498
2	7500 - 7520
3	7522 - 7565
4	7567 - 7653
5	7655 - 7697
6	7699 - 7781
7	7783 - 7787
8	7789 - 7838
9	7840 - 7843
10	7845 - 7875
11	7877 - 7899
12	7901 - 7901
13	7903 - 7933
```
6. Pawan Kumar Khowal July 29, 2016 / 11:28 pm

–SOLUTION 1

;WITH CTE
AS
(
SELECT
Empno
,CASE WHEN Empno – lag(Empno) OVER (ORDER BY Empno) <= 1 THEN 0 ELSE 1 END rnk
FROM Emp
)
,CTE2 AS
(
SELECT Empno,SUM(rnk) OVER (ORDER BY Empno ROWS UNBOUNDED PRECEDING) grouper
FROM CTE
)
SELECT CONCAT( MIN(Empno) , ' – ' , MAX(Empno) ) [Avail. Emp Numbers] FROM CTE2
GROUP BY grouper

–SOLUTION 2

;WITH CTE1 AS
(
SELECT Empno , Empno – ROW_NUMBER() OVER (ORDER BY Empno) Rnk FROM FindGaps
)
SELECT CONCAT( MIN(Empno) , ' – ' , MAX(Empno)) [Avail. Emp Numbers] FROM CTE1
GROUP BY Rnk

–Pawan

• Zohar Elkayam July 30, 2016 / 1:38 am

your second solution assumes no duplication (and return wrong results when it does).
for example, if 7565 appears twice, the result should be something like this:

```1	7370 - 7498
2	7500 - 7520
3	7522 - 7565 <<< first set
4	7565 - 7565 <<< the single duplicate page
5	7567 - 7653
6	7655 - 7697
7	7699 - 7781
8	7783 - 7787
9	7789 - 7838
10	7840 - 7843
11	7845 - 7875
12	7877 - 7899
13	7901 - 7901
14	7903 - 7933
```

```1	7370 – 7498
2	7500 – 7565
3	7522 – 7653 <<< skipped the gap, ignored 7565
4	7655 – 7697
5	7699 – 7781
6	7783 – 7787
7	7789 – 7838
8	7840 – 7843
9	7845 – 7875
10	7877 – 7899
11	7901 – 7901
12	7903 – 7933
```

your first solution detects the gap, but ignore the duplication (the 7565 being a single duplicate page).

cool solution! 🙂

• Pawan Kumar Khowal July 30, 2016 / 6:11 am

By the way we cannot have duplicate values in the empno column.

7. praneeth July 30, 2016 / 3:17 am

SELECT Min_ID||’ – ‘||Max_Id “Avail. Emp Number”
FROM (SELECT Empno+1 Min_Id, LEAD(Empno,1) OVER(ORDER BY Empno)-1 Max_Id FROM Emp)
WHERE Max_ID IS NOT NULL;