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

Three Soluitions to Puzzle of the Week #17

Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

Expected Result:

DEPTNO Year 1   Year 2
------ -------- --------
    10 1981     1982
    20 1980     More (3)
    30 1981

Solutions

#1: Using COUNT(DISTINCT ..)

SELECT deptno, MIN(EXTRACT(YEAR FROM hiredate)) AS "Year 1",
       CASE COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))
            WHEN 1 THEN ''
            WHEN 2 THEN TO_CHAR(MAX(EXTRACT(YEAR FROM hiredate)))
            ELSE 'More (' || COUNT(DISTINCT EXTRACT(YEAR FROM hiredate)) || ')'
       END AS "Year 2"
FROM emp
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

#2: Using DENSE_RANK Analytic Function

WITH x AS (
SELECT deptno, EXTRACT(YEAR FROM hiredate) hire_year,
       DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate)) rk
FROM emp
)
SELECT deptno, MIN(hire_year) "Year 1",
       CASE MAX(rk) WHEN 1 THEN ''
                    WHEN 2 THEN CAST(MAX(hire_year) AS CHAR(4))
                    ELSE 'More (' || MAX(rk) || ')'
       END AS "Year 2"
FROM x
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

#3: Using PIVOT clause

SELECT deptno, Y1 "Year 1",
       CASE WHEN cnt>2 THEN 'More (' || cnt || ')'
       ELSE TO_CHAR(Y2)
       END "Year 2"
FROM
(
SELECT deptno, EXTRACT(YEAR FROM hiredate) yr,
       CASE DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate))
       WHEN 1 THEN 'Y1'
            WHEN 2 THEN 'Y2'
       END AS Y,
       COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))OVER(PARTITION BY deptno) cnt
FROM emp
)
PIVOT
(
  MAX(yr)
  FOR y IN ('Y1' y1,'Y2' y2)
);

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

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.