Hi Tom,

I am trying to write a query for following scenario:
We store history of various steps in recruitment process for all candidates.
The requirement is to produce a listing of a report that will display count of
people for each status (having that status) for each month in last one year.
A person's status can change multiple times in a month. The person should be counted under the last status that he/she held within that month. Effectively a person should be counted only once in a month.
Also,

Here is the table structure & sample data:

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as apps


SQL> create table person_status_history (person_id number, status varchar2(15), status_date date );

Table created

SQL> insert into person_status_history values (1, 'INTERVIEWED', to_date('12-JAN-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (1, 'OFFERED', to_date('2-FEB-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (1, 'JOINED', to_date('27-FEB-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (1, 'CONFIRMED', to_date('27-MAR-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (2, 'INTERVIEWED', to_date('22-JAN-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (2, 'OFFERED', to_date('12-FEB-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (3, 'INTERVIEWED', to_date('15-FEB-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (3, 'OFFERED', to_date('17-MAR-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (3, 'JOINED', to_date('27-MAR-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (4, 'INTERVIEWED', to_date('15-DEC-2006','DD-MON-YYYY')) ;

1 row inserted

SQL> insert into person_status_history values (4, 'REJECTED', to_date('5-JAN-2007','DD-MON-YYYY')) ;

1 row inserted

SQL> commit ;

Commit complete

SQL> select to_char(last_days, 'MONTHYYYY'),
2 substr(max_data, 15) status,
3 count(*)
4 from ( select person_id, last_days, max(to_char(status_date, 'YYYYMMDDHH24MISS') || status) max_data
5 from person_status_history psh,
6 ( select add_months(last_day(to_date('01-MAR-2006', 'DD-MON-YYYY')), (rownum - 1)) last_days
7 from all_objects where rownum < 13 ) mth
8 where psh.status_date <= mth.last_days
9 group by person_id, last_days )
10 group by to_char(last_days, 'MONTHYYYY'), substr(max_data, 15) ;


TO_CHAR(LAST_DAYS,'MONTHYYYY') STATUS COUNT(*)
------------------------------- --------------------------------------------- ----------
DECEMBER 2006 INTERVIEWED 1
FEBRUARY 2007 INTERVIEWED 1
FEBRUARY 2007 JOINED 1
FEBRUARY 2007 OFFERED 1
FEBRUARY 2007 REJECTED 1
JANUARY 2007 INTERVIEWED 2
JANUARY 2007 REJECTED 1

7 rows selected

I am not sure whether this is the best approach.
Can you help please ?

ops$tkyte%ORA10GR2> select trunc(status_date,'mm'), status, count(*)
  2    from (
  3  select person_id, status, status_date,
  4         max(status_date) over (partition by person_id, trunc(status_date,'mm')) max_status_date
  5    from person_status_history
  6         )
  7   where status_date = max_status_date
  8   group by trunc(status_date,'mm'), status
  9   order by 1, 2
 10  /

TRUNC(STA STATUS            COUNT(*)
--------- --------------- ----------
01-DEC-06 INTERVIEWED              1
01-JAN-07 INTERVIEWED              2
01-JAN-07 REJECTED                 1
01-FEB-07 INTERVIEWED              1
01-FEB-07 JOINED                   1
01-FEB-07 OFFERED                  1
01-MAR-07 CONFIRMED                1
01-MAR-07 JOINED                   1

8 rows selected.