Puzzle of the Week #5 – Calendar Hiring Report

Puzzle of the Week #5:

Write a single SELECT statement that would list all 12 months and number of employees hired in each month. Year part should be ignored. Use emp table (in scott schema). Find as many solutions as possible.

Expected Result:

Month  Number of hires
------ ---------------
JAN                  1
FEB                  2
MAR                  0
APR                  2
MAY                  2
JUN                  1
JUL                  0
AUG                  0
SEP                  2
OCT                  0
NOV                  1
DEC                  3

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.

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.

Advertisements

5 thoughts on “Puzzle of the Week #5 – Calendar Hiring Report

  1. Muhammad Farooq March 26, 2016 / 4:52 am

    SELECT HIRE_MONTH,EMPCOUNT FROM(
    selecT
    TO_NUMBER(to_char(e.HIREDATE,’MM’)) MONSORT,
    to_char(e.HIREDATE,’MON’) HIRE_MONTH,
    COUNT(EMPNO) EMPCOUNT from emp e
    GROUP BY to_char(e.HIREDATE,’MON’),
    TO_NUMBER(to_char(e.HIREDATE,’MM’))
    ORDER BY TO_NUMBER(to_char(e.HIREDATE,’MM’))
    )

  2. Ashwini Kumar March 27, 2016 / 10:35 pm

    select nvl(emp.Months,mn.Months),nvl(CNT,0) from
    (select extract(Month from hiredate) id,to_char(hiredate,’MON’) Months,count(*) cnt from emp
    group by extract(Month from hiredate),to_char(hiredate,’MON’) ) emp,
    (SELECT to_char(to_date( level,’mm’), ‘MON’) Months,level id FROM DUAL CONNECT BY LEVEL <=12) mn
    where mn.id=emp.id(+) order by mn.id;

  3. Ashwini Kumar March 28, 2016 / 9:19 pm

    select nvl(emp.Months,mn.Months),nvl(CNT,0) from
    (select extract(Month from hiredate) id,to_char(hiredate,’MON’) Months,count(*) cnt from emp
    group by extract(Month from hiredate),to_char(hiredate,’MON’) ) emp,
    (SELECT to_char(to_date( level,’mm’), ‘MON’) Months,level id FROM DUAL CONNECT BY LEVEL <=12) mn
    where mn.id=emp.id(+) order by mn.id;

  4. sunitha March 28, 2016 / 10:05 pm

    select x.mon Month,
    count(hiredate) hires_per_month from
    scott.emp a,
    (SELECT to_char(TRUNC(SYSDATE,’y’)-1+ROWNUM,’Mon’) mon, to_char(TRUNC(SYSDATE,’y’)-1+ROWNUM,’mm’) mm
    FROM all_objects
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y')
    group by to_char(TRUNC(SYSDATE,'y')-1+ROWNUM,'Mon') , to_char(TRUNC(SYSDATE,'y')-1+ROWNUM,'mm')
    ) x
    where x.mon = to_char(hiredate(+),'Mon')
    group by x.mon, x.mm order by x.mm;

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