Generating dates between 2 given dates and looping in PL/SQL

Question

I am very new to PL/SQL. I have created a table in Oracle database and based on that table, I have to generate a report to show the number of open bugs from 5/1/2013 through 5/31/2013, and at the end of the report, need to display the maximum number of open bugs on a single day (using PL/SQL anonymous block). Table definition and data is below:

CREATE TABLE bugs
(
BUG_ID          NUMBER PRIMARY KEY,
REPORTED_DATE   DATE NOT NULL,
DESCRIPTION     VARCHAR2(20),
PRIORITY        NUMBER(2),
ASSIGNED_TO     VARCHAR2(10),
CLOSED_DATE     DATE,
NOTE            VARCHAR2(20)
);

INSERT INTO BUGS VALUES (1230, '25-APR-13', NULL, 3, 'Team 3', '28-APR-13', NULL); 
INSERT INTO BUGS VALUES (1231, '29-APR-13', NULL, 1, 'Team 1', '29-APR-13', NULL); 
INSERT INTO BUGS VALUES (1232, '03-MAY-13', NULL, 1, 'Team 1', '03-MAY-13', NULL); 
INSERT INTO BUGS VALUES (1233, '03-MAY-13', NULL, 1, 'Team 3', '08-MAY-13', NULL);
INSERT INTO BUGS VALUES (1234, '04-MAY-13', NULL, 2, 'Team 5', '15-MAY-13', NULL);
INSERT INTO BUGS VALUES (1235, '04-MAY-13', NULL, 2, 'Team 1',  NULL,       NULL);
INSERT INTO BUGS VALUES (1236, '05-MAY-13', NULL, 1, 'Team 2', '06-MAY-13', NULL);
INSERT INTO BUGS VALUES (1237, '05-MAY-13', NULL, 3, 'Team 3', '10-MAY-13', NULL);
INSERT INTO BUGS VALUES (1238, '09-MAY-13', NULL, 4, 'Team 5', '16-MAY-13', NULL);
INSERT INTO BUGS VALUES (1239, '09-MAY-13', NULL, 5, 'Team 6',  NULL,       NULL);
INSERT INTO BUGS VALUES (1240, '12-MAY-13', NULL, 5, 'Team 2', '30-MAY-13', NULL);
INSERT INTO BUGS VALUES (1241, '12-MAY-13', NULL, 1, 'Team 1', '20-MAY-13', NULL);
INSERT INTO BUGS VALUES (1242, '13-MAY-13', NULL, 4, 'Team 4', '25-MAY-13', NULL);
INSERT INTO BUGS VALUES (1243, '14-MAY-13', NULL, 4, 'Team 3', '01-JUN-13', NULL);
INSERT INTO BUGS VALUES (1244, '14-MAY-13', NULL, 2, 'Team 4', '25-MAY-13', NULL);
INSERT INTO BUGS VALUES (1245, '20-MAY-13', NULL, 2, 'Team 4',  NULL,       NULL);
INSERT INTO BUGS VALUES (1246, '22-MAY-13', NULL, 2, 'Team 4', '25-MAY-13', NULL);
INSERT INTO BUGS VALUES (1247, '25-MAY-13', NULL, 2, 'Team 1', '29-MAY-13', NULL);
INSERT INTO BUGS VALUES (1248, '30-MAY-13', NULL, 1, 'Team 1', '01-JUN-13', NULL);
INSERT INTO BUGS VALUES (1249, '05-JUN-13', NULL, 1, 'Team 2', '07-JUN-13', NULL);
COMMIT;

“Open Bugs” - A bug is considered open on a given day if (1) its “REPORTED_DATE” is on or before that day, and (2) its “CLOSED_DATE” is on or after that day (or is unknown (NULL)). For example, we have 5 open bugs on 5/5/2013.

The output of the program should look like the following:

Date      Number of Open Bugs   
01-MAY-13            0
02-MAY-13            0
03-MAY-13            2  
04-MAY-13            3
05-MAY-13            5
06-MAY-13            5
07-MAY-13            4
08-MAY-13            4
---------            --
---------            --

The maximum number of open bugs on a single day is 9.
There were 9 open bugs on 14-MAY-13.
There were 9 open bugs on 15-MAY-13.
There were 9 open bugs on 25-MAY-13.

I have searched and found that there is ADD_MONTHS and CONNECT_BY_LEVEL functions that I can use, to generate the list of days in the month and then use a loop to count the open bugs on that particular day of the month, but do not know how to exactly use them. Can someone please help me get started. Thanks in advance.

Answer 1

Using the data that you have provided:

Query 1:

with dates as (select to_date('01-MAY-2013', 'DD-MON-YYYY') + level - 1 d_date
                 from dual
               connect by level <= add_months(to_date('01-MAY-2013', 'DD-MON-YYYY'), 1)
                                   - to_date('01-MAY-2013', 'DD-MON-YYYY'))
select d_date, count(bug_id) NO_OF_OPEN_BUGS
  from dates
  left outer join bugs on (d_date between REPORTED_DATE and nvl(CLOSED_DATE, d_date))
group by d_date
order by d_date

Output

|                     D_DATE | NO_OF_OPEN_BUGS |
|----------------------------|-----------------|
| May, 01 2013 00:00:00+0000 |               0 |
| May, 02 2013 00:00:00+0000 |               0 |
| May, 03 2013 00:00:00+0000 |               2 |
| May, 04 2013 00:00:00+0000 |               3 |
| May, 05 2013 00:00:00+0000 |               5 |
| May, 06 2013 00:00:00+0000 |               5 |
| May, 07 2013 00:00:00+0000 |               4 |
| May, 08 2013 00:00:00+0000 |               4 |
| May, 09 2013 00:00:00+0000 |               5 |
| May, 10 2013 00:00:00+0000 |               5 |
| May, 11 2013 00:00:00+0000 |               4 |
| May, 12 2013 00:00:00+0000 |               6 |
| May, 13 2013 00:00:00+0000 |               7 |
| May, 14 2013 00:00:00+0000 |               9 |
| May, 15 2013 00:00:00+0000 |               9 |
| May, 16 2013 00:00:00+0000 |               8 |
| May, 17 2013 00:00:00+0000 |               7 |
| May, 18 2013 00:00:00+0000 |               7 |
| May, 19 2013 00:00:00+0000 |               7 |
| May, 20 2013 00:00:00+0000 |               8 |
| May, 21 2013 00:00:00+0000 |               7 |
| May, 22 2013 00:00:00+0000 |               8 |
| May, 23 2013 00:00:00+0000 |               8 |
| May, 24 2013 00:00:00+0000 |               8 |
| May, 25 2013 00:00:00+0000 |               9 |
| May, 26 2013 00:00:00+0000 |               6 |
| May, 27 2013 00:00:00+0000 |               6 |
| May, 28 2013 00:00:00+0000 |               6 |
| May, 29 2013 00:00:00+0000 |               6 |
| May, 30 2013 00:00:00+0000 |               6 |
| May, 31 2013 00:00:00+0000 |               5 |

Query 2:

with dates as (select to_date('01-MAY-2013', 'DD-MON-YYYY') + level - 1 d_date
                 from dual
               connect by level <= add_months(to_date('01-MAY-2013', 'DD-MON-YYYY'), 1)
                                - to_date('01-MAY-2013', 'DD-MON-YYYY')),
tab as (select d_date, count(bug_id) NO_OF_OPEN_BUGS, dense_rank() over (order by count(bug_id) desc) MAX_FLAG
  from dates
  left outer join bugs on (d_date between REPORTED_DATE and nvl(CLOSED_DATE, d_date))
group by d_date)
select 'There were ' || NO_OF_OPEN_BUGS || 
       ' open bugs on '|| to_char(d_date, 'DD-MON-YYYY') ||
       '.' MSG from 
   tab where max_flag = 1;

Output:

|                                    MSG |
|----------------------------------------|
| There were 9 open bugs on 14-MAY-2013. |
| There were 9 open bugs on 25-MAY-2013. |
| There were 9 open bugs on 15-MAY-2013. |

Information from query 1 is used to generate query 2.

Answer 2

Getting the open bugs per day is fairly simple, with plain SQL (so PL/SQl not required):

with dates as (
  select date '2013-05-01' + level - 1 as q_date
  from dual
  connect by level <= date '2013-05-01' + interval '1' month
    - date '2013-05-01'
)
select d.q_date,
  count(case when b.reported_date < d.q_date + 1
      and (b.closed_date is null or b.closed_date >= d.q_date)
    then 1 end) as open_bugs
from dates d
cross join bugs b
group by d.q_date
order by d.q_date;

SQL Fiddle.

The second part of your output could be done by running the same query three times and adjusting what's selected, but that seems wasteful. You could use a block to query this data into a collection once and then use that to display the list, the top count and then the dates matching that count.