1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

number of employees per month

Discussion in 'SQL PL/SQL' started by paulo-450, Mar 24, 2015.

  1. paulo-450

    paulo-450 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    need help with this anonymous block

    Develop a PL / SQL anonymous block to display the year in which he was admitted a larger number of employees and later, how many employees were hired each month that year

    Example Output:

    YEAR: 1997

    MONTH: 1 ADMITTED EMPLOYEES: 3
    MONTH: 2 ADMITTED EMPLOYEES: 2
    MONTH: 3 ADMITTED EMPLOYEES: 6
    MONTH: 4 ADMITTED EMPLOYEES: 1
    MONTH: 5 ADMITTED EMPLOYEES: 0
    MONTH: 6 ADMITTED EMPLOYEES: 2
    MONTH: 7 ADMITTED EMPLOYEES: 2
    MONTH: 8 ADMITTED EMPLOYEES: 8
    MONTH: 9 ADMITTED EMPLOYEES: 2
    MONTH: 10 ADMITTED EMPLOYEES: 3
    MONTH: 11 ADMITTED EMPLOYEES: 1
    MONTH: 12 ADMITTED EMPLOYEES: 2

    table data:
    id_empregado number, admission date

    THANKS
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    paulo-450 likes this.
  3. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello

    It would be helpful to build new query if you provide the sample data and expected output for the ...
     
    paulo-450 likes this.
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    create or replace procedure proc(i_emp_name varchar2)
    as

    begin

    for rec in (select to_char(hire_date,'RRRR') as j_year from employees
    where emp_name=i_emp_name) loop

    for rec1 in (select to_char(hire_date,'MM') as j_month, count(1) as j_count
    FROM EMPLOYEES WHERE TO_CHAR(hire_date,'RRRR')=REC.J_YEAR
    group by TO_CHAR(hire_date,'MM')) loop

    DBMS_OUTPUT.PUT_LINE('MONTH: '||REC1.J_MONTH|| 'ADMITTED EMPLOYEES: '||REC1.J_COUNT);

    end loop;

    end loop;

    end;
     
    paulo-450 likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    But, Its repeating the results 3 times., Cud any 1 tel why it happening and how to avoid it or any other better way to achieve it/.!
     
    paulo-450 likes this.
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hii., It's working now., It's because of the repetition of emp_name ;)
     
    paulo-450 likes this.
  7. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    DECLARE
       cursor c
       IS
       SELECT EXTRACT(YEAR FROM hire_date) y,
              EXTRACT(MONTH FROM hire_date) m,
              COUNT(*) c
       FROM employees
       GROUP BY EXTRACT(YEAR FROM hire_date),
                EXTRACT(MONTH FROM hire_date)
       ORDER BY 1,2;
       --
       l_year NUMBER;
    BEGIN
       FOR a IN c loop
          IF l_year IS NULL OR l_year != a.y THEN
             dbms_output.put_line(''); --just empty line
             dbms_output.put_line('YEAR: '||to_char(a.y));
             dbms_output.put_line(''); --just empty line
             l_year := a.y;
          END IF;
          --
          dbms_output.put_line('MONTH: '||to_char(a.m)||' ADMITTED EMPLOYEES: '||to_char(a.c));
       END loop;
    END;
     
     
    paulo-450 likes this.
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are very lucky someone did your homework for you. You cannot expect us to do your classwork for you. Feel free to post such questions but, in doing so, also post the work YOU have done. We will assist you in finding where you went wrong but we will not again do your assignments.




    David Fitzjarrell
     
  9. paulo-450

    paulo-450 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    thank you for the help, but I will not deny that this is part of an exercise that already delivered , and the reason for not having posted no code is for 20 issues this is the one who froze, even this was not the hardest .And as only receive the fix here the month , I wonder what needed to be done, but if the topic was not within the rules was just not respond or delete it