DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Total number of requests per day of the week

  1. #1
    Join Date
    Sep 2002
    Posts
    29

    Total number of requests per day of the week

    I'm trying to determine if I can calculate the total number of trouble tickets that occur for each day of the week and for the entire year. I'm not sure how to write this query or if it can be done. I'm on 8.1.6 and running Windows 2000. Please excuse my lack of knowledge because I'm very new to Oracle and PL/SQL. The column I need totals from is named open_dt(date data type 1/18/2000 1:16:00 PM). What I'd like to determine is the busiest day of the week for trouble ticket creation throughout the entire year. This will allow us to get a better idea of staffing needs. Thanks for your help.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Here is a start for you. You'll want to see what the parts of a PL/SQL block are, how to do loops, how to output results, and how to do date/character manipulation (how to get the day of the week). There are many other ways to do this, and the output can be formatted to state the day (Monday, Tuesday, etc.) instead of day 1, day 2, etc.

    DECLARE
    day_count NUMBER;

    BEGIN
    FOR i in 1..7
    LOOP
    select count(*) into day_count
    from ticket_table_name
    where to_char(open_dt, 'D') = i;

    dbms_output.put_line('Tickets for day of week '||i||' = '||day_count);
    END LOOP;

    END;
    Last edited by stecal; 12-30-2002 at 05:53 PM.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    Proof of concept:

    Code:
    SQL> select ename, hiredate from emp;
    
    ENAME      HIREDATE
    ---------- ---------
    CRAFT      02-NOV-02
    WARD       22-FEB-81
    JONES      02-APR-81
    MARTIN     28-SEP-81
    BLAKE      01-MAY-81
    CLARK      09-JUN-81
    SCOTT      19-APR-87
    KING       17-NOV-81
    TURNER     08-SEP-81
    ADAMS      23-MAY-87
    JAMES      03-DEC-81
    FORD       03-DEC-81
    MILLER     23-JAN-82
    
    13 rows selected.
    
    SQL> DECLARE
      2  day_count NUMBER;
      3  
      4  BEGIN
      5  FOR i in 1..7 
      6  LOOP
      7  select count(*) into day_count
      8  from emp
      9  where to_char(hiredate, 'D') = i;
     10  
     11  dbms_output.put_line('Hires for day of week '||i||' = '||day_count);
     12  END LOOP;
     13  
     14  END;
     15  /
    Hires for day of week 1 = 2
    Hires for day of week 2 = 1
    Hires for day of week 3 = 3
    Hires for day of week 4 = 0
    Hires for day of week 5 = 3
    Hires for day of week 6 = 1
    Hires for day of week 7 = 3
    
    PL/SQL procedure successfully completed.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    this would do

    Code:
    SQL> select to_char(hiredate, 'D') WEEKDAY, count(*) hired
      2  from emp
      3  group by to_char(hiredate, 'D');
    
    WEEKDAY                   HIRED
    -------------------- ----------
    1                             2
    2                             3
    3                             2
    4                             3
    5                             2
    7                             2

  5. #5
    Join Date
    Sep 2002
    Posts
    29
    Thanks for the help.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Code:
    SQL> select decode(to_char(hiredate, 'D'),1,'SUNDAY', 
      2  2,'MONDAY', 3,'TUESDAY',4,'WEDNESDAY',
      3  5,'THURSDAY', 6,'FRIDAY',7,'SATURDAY') WEEKDAY, 
      4  count(*) hired
      5  from emp
      6  group by to_char(hiredate, 'D')
      7  ;
    
    WEEKDAY        HIRED
    --------- ----------
    SUNDAY             2
    MONDAY             1
    TUESDAY            3
    THURSDAY           3
    FRIDAY             1
    SATURDAY           3
    
    6 rows selected.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont need decode

    Code:
    SQL> select to_char(hiredate, 'DAY') WEEKDAY, count(*) hired
      2  from emp
      3  group by to_char(hiredate, 'DAY');
    
    WEEKDAY        HIRED
    --------- ----------
    FRIDAY             2
    MONDAY             2
    SUNDAY             2
    THURSDAY           3
    TUESDAY            3
    WEDNESDAY          2

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Yes, but the days of the week are not in the order we normally associate with this type of list. Days of the week in alphabetical order does nothing for us in this query. And besides, he asked about using PL/SQL.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the problem with decode is depending on NLS settings you can get wrong days, for example nls_lang set to AMERICAN I think you get day 1 as Sunday however nls_lang SPANISH would get 1 as Monday

    I guess he asked PL/SQL because he doesnt know how to do it in SQL

  10. #10
    Join Date
    Sep 2002
    Posts
    29
    Actually I didn't know how to do it in either one. I tried Pando's script
    SQL> select to_char(hiredate, 'D') WEEKDAY, count(*) hired
    2 from emp
    3 group by to_char(hiredate, 'D');

    and than I used Stecal's

    SQL> select decode(to_char(hiredate, 'D'),1,'SUNDAY',
    2 2,'MONDAY', 3,'TUESDAY',4,'WEDNESDAY',
    3 5,'THURSDAY', 6,'FRIDAY',7,'SATURDAY') WEEKDAY,
    4 count(*) hired
    5 from emp
    6 group by to_char(hiredate, 'D')
    7 ;

    Pando'd placed the results in the proper order in comparison with Stecal's. Having the actual name of the day of the week was an added bonus. Thanks again for both of your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width