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!

Query to get multiple counts and percentage in single query

Discussion in 'SQL PL/SQL' started by venalla_shine, Dec 8, 2010.

  1. venalla_shine

    venalla_shine Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi, I need to write a query for a report to show distinct count of employees with registration date , count of employess with registration date less than today's date followed by percentage for the same, count of employess with registration date less than a month from today followed by percentage

    I wrote the below shown code.My problem is the entire query executes very slow it takes 4 hours to pull the result sets. Can anyone think of an other efficient way to rewrite the code on Oracle 11g.

    Table
    [TABLE]state department EMP_ID date_reg
    NJ MD 1111 1/1/2010
    NJ MD 1111 1/2/2011
    NJ MD 1112 1/2/2009
    NJ MD 1112 1/2/2008
    NJ MD 1113 1/2/2002
    NJ MD 1114 1/2/2012
    NJ DO 2222 1/1/2009
    NJ PO 3333 2/2/2008
    NJ PO 3333 3/3/2009[/TABLE]

    [TABLE]state department total_emp total_emp_reg_before_today(sysdate) total_emp_reg_before_(today)pct total_emp_reg_before_month(sysdate+30) total_emp_reg_before_month(sysdate+30)pct
    NJ MD 4 2 50% 2 50%[/TABLE]

    I wrote the below query

    select v.state,v.department,
    reg_cnt_new(v.state,v.department,0) a,
    reg_cnt_new(v.state,v.department,1) b,
    (round((decode(reg_cnt_new(v.state,v.department,0),0,0,reg_cnt_new(v.state,v.department,1)/reg_cnt_new(v.state,v.department,0)))*100,2)) as b_pct
    reg_cnt_new(v.state,v.department,30) c,
    (round((decode(reg_cnt_new(v.state,v.department,30),0,0,reg_cnt_new(v.state,v.department,30)/reg_cnt_new(v.state,v.department,0)))*100,2)) as c_pct
    from reg_exp_rpt v
    group by v.state,v.department;

    Function reg_cnt_new
    CREATE OR REPLACE FUNCTION reg_cnt_new(v_state IN VARCHAR2,
    v_dept IN VARCHAR2,
    v_reg_day IN NUMBERMBER
    ) RETURN NUMBER IS
    v_reg_cnt NUMBER DEFAULT 0;

    v_reg_day NUMBER DEFAULT 0;

    CURSOR reg_exp(v_state IN varchar2,v_dept IN varchar2) IS
    SELECT count(distinct emp_id)
    FROM reg_exp_rpt c
    WHERE c.state = v_state
    AND c.department= v_dept
    AND c.date_reg IS NOT NULL
    ;

    CURSOR reg_exp_todt(v_state IN varchar2,v_dept IN varchar2) IS

    select count(*) FROM
    (
    SELECT emp_id
    FROM reg_exp_rpt c
    WHERE c.state = v_state
    AND c.department= v_dept
    AND c.date_reg IS NOT NULL<= SYSDATE
    MINUS
    SELECT emp_id
    FROM reg_exp_rpt c
    WHERE c.state = v_state
    AND c.department= v_dept
    AND c.date_reg IS NOT NULL> SYSDATE
    )
    ;

    CURSOR reg_exp_30(v_state IN varchar2,v_dept IN varchar2) IS
    select count(*) FROM
    (
    SELECT emp_id
    FROM reg_exp_rpt c
    WHERE c.state = v_state
    AND c.department= v_dept
    AND c.date_reg IS NOT NULL<= (SYSDATE+30)
    MINUS
    SELECT emp_id
    FROM reg_exp_rpt c
    WHERE c.state = v_state
    AND c.department= v_dept
    AND c.date_reg IS NOT NULL> (SYSDATE+30)
    )
    ;

    BEGIN
    /* Count of Profiles with reg date and empid*/
    IF (v_reg_day = 0) THEN

    OPEN reg_exp(v_state,v_dept);
    LOOP

    FETCH reg_exp INTO v_reg_cnt;

    EXIT WHEN reg_exp%NOTFOUND;

    END LOOP;
    CLOSE reg_exp;

    /* Count of Profiles where registration less than SYSDATE*/
    ELSIF (v_reg_day = 1) THEN

    OPEN reg_exp_todt(v_state,v_dept);
    LOOP

    FETCH reg_exp_todt INTO v_sln_cnt;
    EXIT WHEN sln_reg_todt%NOTFOUND;

    END LOOP;
    CLOSE reg_exp_todt;

    /* Count of Profiles where registration less than SYSDATE+30*/
    ELSIF(v_reg_day = 30) THEN
    v_add_day := v_reg_day;
    OPEN reg_exp_30(v_state,v_dept);
    LOOP

    FETCH reg_exp_30 INTO v_reg_cnt;
    EXIT WHEN reg_exp_30%NOTFOUND;

    END LOOP;
    CLOSE reg_exp_30;


    END IF;

    RETURN v_reg_cnt;

    EXCEPTION
    WHEN others THEN
    RETURN 0;
    END;
    /
     
  2. nsramu

    nsramu Active Member

    Messages:
    3
    Likes Received:
    2
    Trophy Points:
    85
    does use of Ref cursors reduce the time? Please can you check. thanks.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi,

    There are lots of dependencies before we answer this question.

    1. How much is the Volume of the data in the table "reg_exp_rpt"
    2. Are the columns "state" and "department" prperly Indexed ?
    3. Is the table Analysed recently ?

    Anyway Refcursor will not be helping you for Performance improvement.

    I Hope the foll0wing caluses are Typo .

    Code (SQL):
     c.date_reg IS NOT NULL<= SYSDATE
     c.date_reg IS NOT NULL> SYSDATE
     c.date_reg IS NOT NULL<= (SYSDATE+30)
     c.date_reg IS NOT NULL> (SYSDATE+30)
     
    Also you can try "EXISTS" Clause instead of "MINUS".

    Also you can try Something like as below with "WITH" clause.


    Code (SQL):
    WITH REC AS(
    SELECT v.state,v.department,
    reg_cnt_new(v.state,v.department,0) a,
    reg_cnt_new(v.state,v.department,1) b,
    reg_cnt_new(v.state,v.department,30) c
    FROM reg_exp_rpt v
    GROUP BY v.state,v.department)
    SELECT state,
               department,
               a,
               b,
               (round((decode(a,0,0,b/a))*100,2)) AS b_pct,
               c,
               (round((decode(c,0,0,c/a))*100,2)) AS c_pct
    FROM REC
     
    Actually I have some doubts on your programmig logic as well :)

    Code (SQL):
    b_pct IS (round((decode(a,0,0,b/a))*100,2)) while
    b_pct IS (round((decode(c,0,0,c/a))*100,2)).
     
     
  4. venalla_shine

    venalla_shine Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Response to rajavu questions

    1) The volume of data is close to 5.1 million rows.
    2) Yes, they are indexed and everytime the table is built they get dropped and recreated.
    3) Yes, the table does get analysed each time its dropped and rebuilt.

    Agreed, Cursors do not boost performance.

    As far as the logic, that's the reason, I have posted the code in this forum for some of you geniuses to share your perspective of logic you would choose to do.

    Ok, let me describe my problem in a simpler way.

    State| Department Name | # of employees | % of Total |# of employees (today) |% of Tday| # of employees (month) |% of month| # of employees (2 month) |% of 2 MOnth | # of employees (3 month)|% of (3 Month)

    The "% of Total" field would answer the following question:

    What percentage are/were the employees in a department by state for a given date, Month, 2 month and 3 months.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This performance issue might be because of current usage of date fields in the WHERE clause.

    Try Using BETWEEN Clause instead of <= and >= statements against the date fields.