SQL & PL/SQL

Hello Gurus,

We have a table TIMESHEET_ARCHIVE with all employees effort logged in for the days they have worked. A sample data is given below, if the employee is on leave or not worked for any particular day(this includes weekend and employee leave days) then there will not be any record for that particular day as shown below.

TD_date Emp_id Effort Archive_month
=====-----=====-----===-----=========
1-Mar-13-----123-----8-----Mar-13
4-Mar-13-----123-----8-----Mar-13
5-Mar-13-----123-----8-----Mar-13
6-Mar-13-----123-----8-----Mar-13
7-Mar-13-----123-----8-----Mar-13
8-Mar-13-----123-----8-----Mar-13
11-Mar-13----123-----8-----Mar-13
1-Mar-13-----213-----8-----Mar-13
3-Mar-13-----213-----8-----Mar-13
5-Mar-13-----213-----8-----Mar-13
6-Mar-13-----213-----8-----Mar-13
7-Mar-13-----213-----8-----Mar-13
8-Mar-13-----213-----8-----Mar-13
9-Mar-13-----213-----8-----Mar-13
11-Mar-13----213-----8-----Mar-13
1-Mar-13-----312-----8-----Mar-13
2-Mar-13-----312-----8-----Mar-13
4-Mar-13-----312-----8-----Mar-13
7-Mar-13-----312-----8-----Mar-13
8-Mar-13-----312-----8-----Mar-13
9-Mar-13-----312-----8-----Mar-13

In the above sample data,
The employee 123 has worked on all working days and did not work on the weekends (2nd, 3rd, 9th and 10th March 2013). So he has not worked for two consecutive days.
The employee 213 has worked on all days except 2nd, 4th and 10th March 2013. He has taken leave on three different days of a week, that is he has not taken two consecutive days off.
The employee 312 has taken leave on 3rd, 5th, 6th and 10th March 2013. He has taken two consecutive days off (5th and 6th).

In a requirement i have to list the employees who had not taken two consecutive days off. As per this requirement only the employee 213 should be listed among the sample data as output

Emp_id
213

Please help me with an approach in SQL or PL/SQL to generate a report of employees who has not taken two consecutive days off for amy given ARCHIVE_MONTH (sample - MAR-13).This requirement includes in all days for any given month(March 2013), if an employee has not worked for two consecutive days, he should not be listed in the output.

Please find below the create and insert scripts for the sample data:

create table timesheet_archive
(TD_date date,
Emp_id number(19,0),
Effort Float,
Archive_month varchar2(9));

insert into timesheet_archive values (to_date('1-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('4-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('5-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('6-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('7-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('8-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
insert into timesheet_archive values (to_date('11-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');

insert into timesheet_archive values (to_date('1-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('3-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('5-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('6-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('7-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('8-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
insert into timesheet_archive values (to_date('11-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');

insert into timesheet_archive values (to_date('1-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');
insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');
insert into timesheet_archive values (to_date('4-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');
insert into timesheet_archive values (to_date('7-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');
insert into timesheet_archive values (to_date('8-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');
insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 312,8,'03/2013');

Thanks,

Edited by: 999145 on Apr 15, 2013 6:19 AM