Puzzle of the Week #18: Find available employee numbers

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

To submit your answer (one or more!) please start following this blog and add a comment to this post.

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.

Advertisements

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
    );
    

    To learn more about Oracle 12c Pattern Matching, join me in my session about Advanced SQL Techniques in Oracle Open World 2016!

  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
      [...]
      

      instead of

      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

    ohh…. my bad…Pasted something else..

    –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
      

      but your code returns

      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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s