Puzzle of the Week #17: Show hiring years

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

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

4 thoughts on “Puzzle of the Week #17: Show hiring years

  1. Krishna Jamal June 28, 2016 / 2:36 pm

    WITH t AS (SELECT Deptno,
    Years,
    DENSE_RANK() OVER(PARTITION BY Deptno ORDER BY Years) Dnk
    FROM (SELECT Deptno,
    EXTRACT(Year FROM HireDate) Years
    FROM Emp))
    SELECT Deptno,
    DECODE(Rnk1, 1, y1) Year1,
    DECODE(Rnk2, 1, NULL, 2, Y2, ‘More(‘||Rnk2||’)’) Year2
    FROM ( SELECT Deptno,
    MIN(Dnk) Rnk1,
    MIN(Years) y1,
    MAX(Dnk)Rnk2,
    MAX(Years) y2
    FROM t
    GROUP BY Deptno)

  2. Krishna Jamal June 30, 2016 / 3:42 am

    WITH t AS
    (SELECT Deptno, EXTRACT(Year FROM HireDate) yr,
    DENSE_RANK() OVER(PARTITION BY Deptno ORDER BY EXTRACT(Year FROM HireDate)) Rnk
    FROM Emp)
    SELECT Deptno, MIN(DECODE(Rnk, 1, yr)) Year1, MAX(DECODE(Rnk, 1, NULL, 2, yr, ‘More(‘||Rnk||’)’)) Year2
    FROM t
    GROUP BY Deptno;

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