Total number of requests per day of the week

[brado]

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.

[stecal]

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;

[stecal]

Proof of concept:

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.

[pando]

this would do

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

[brado]

Thanks for the help.

[stecal]

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.

[pando]

you dont need decode

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

[stecal]

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.

[pando]

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

[brado]

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.