Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

```years
------------
1975 - 1979
1983 - 1986
1988 - 2016```

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

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

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

6 thoughts on “Puzzle of the Week #15”

1. Krishna Jamal June 14, 2016 / 1:44 pm

WITH t (yr) AS(
SELECT TO_CHAR(1975+LEVEL-1)
FROM Dual
CONNECT BY LEVEL <= TO_CHAR(SYSDATE, 'YYYY')-1975+1
MINUS
SELECT TO_CHAR(HireDate, 'YYYY') FROM Emp)
SELECT MIN(yr)||' – '||MAX(yr) "Years"
FROM(SELECT yr, yr+ROWNUM*(-1) Rng FROM t)
GROUP BY Rng
ORDER BY 1;

2. Krishna Jamal June 17, 2016 / 3:51 pm

WITH t (y) AS(
SELECT TO_CHAR(1975+LEVEL-1) FROM Dual
CONNECT BY LEVEL <= TO_CHAR(SYSDATE, 'YYYY')-1975+1
MINUS
SELECT TO_CHAR(HireDate, 'YYYY') FROM Emp ORDER BY 1)
SELECT MIN(y)||' – '||MAX(y) "Years"
FROM ( SELECT y, SUM(csn) OVER(ORDER BY y) Rng
FROM ( SELECT y, CASE WHEN y-LAG(y) OVER(ORDER BY y) = 1 THEN 0 ELSE 1 END csn
FROM t)
)
GROUP BY Rng;

3. Krishna Jamal June 18, 2016 / 3:07 am

WITH t (y) AS(
SELECT TO_CHAR(1975+LEVEL-1) FROM Dual
CONNECT BY LEVEL <= TO_CHAR(SYSDATE, 'YYYY')-1975+1
MINUS
SELECT TO_CHAR(HireDate, 'YYYY') FROM Emp ORDER BY 1)
SELECT a.y||' – '||b.y "Years" FROM
( SELECT ROW_NUMBER() OVER(ORDER BY a.y) rn, a.y
FROM t a LEFT OUTER JOIN t b ON a.y = b.y+1
WHERE b.y IS NULL) a,
( SELECT ROW_NUMBER() OVER(ORDER BY a.y) rn, a.y
FROM t a LEFT OUTER JOIN t b ON a.y = b.y-1
WHERE b.y IS NULL) b
WHERE a.rn = b.rn;

• Zahar Hilkevich June 20, 2016 / 9:52 am

This is an Oracle SQL puzzle, it uses very well known Oracle built-in scott schema. You can google scott schema scripts online to find ddl/dml for it.