Puzzle of the Week #15

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

 

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

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.

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