help with this query !!!

[itguru1]

Hi
I have a table with 2 columns : emp_id and leave_date. for each leave, one row is inserted into teh table with the emp_id. i have to find all those employees who have taken 10 consecutive leaves.

can anyone out there help me out !!

thanks in advance

 

[cliousa]

Sure. That's a easy one. Here is a test case and the query:


SQL> create table employee (emp_id number, leave_date date);

Table created.

SQL> insert into employee values (1, sysdate-10);

1 row created.

SQL> insert into employee values (1, sysdate-9);

1 row created.

SQL> insert into employee values (1, sysdate-8);

1 row created.

SQL> insert into employee values (1, sysdate-7);

1 row created.

SQL> commit;

SQL> select a.emp_id from employee a, employee b
2 where a. emp_id = b.emp_id
3 having count(a.leave_date) >= 3
4 group by a.emp_id;

EMP_ID
----------
1


Hope that helps,

clio_usa - OCP DBA

------------------

http://www.dbaclick.com

Oracle articles, scripts, Usenet Newsgroups

 

[djbjr]

If the question is asking what I think it is asking, you want to check for 10 CONSECUTIVE days correct? In that case this code will work:

SELECT EMPLID, FIRST_DAY_OUT, LAST_DAY_OUT
FROM (SELECT emp_id AS EMPLID
,(FIRST_VALUE(emp_id) OVER(ORDER BY emp_id, leave_date ROWS 9 PRECEDING)) AS PREV_EMPLID
,(FIRST_VALUE(leave_date) OVER(ORDER BY emp_id, leave_date ROWS 9 PRECEDING)) AS FIRST_DAY_OUT
, leave_date AS LAST_DAY_OUT
,CASE WHEN to_char(leave_date, 'd') = 6
THEN DECODE(leave_date - (FIRST_VALUE(leave_date) OVER(ORDER BY emp_id, leave_date ROWS 9 PRECEDING)), 11, 'Y', 'N')
ELSE DECODE(leave_date - (FIRST_VALUE(leave_date) OVER(ORDER BY emp_id, leave_date ROWS 9 PRECEDING)), 13, 'Y', 'N') END AS TEN_DAY_INDICATOR
FROM TEST)
WHERE TEN_DAY_INDICATOR = 'Y'
AND emplid = prev_emplid


What I am doing is using an inline view to create a table that checks the days between the leave_date and the leave_date 9 rows prior for a total of ten leave dates by using a windowing function. I added logic to check the day of the week and not cound weekends as consecutive days. (That is what the case statement is doing)
If the total number of days between is 13 (or 11 if the current row is a Friday), it sets the ten_day_indicator flag to 'Y'. All that is left to do is verify that the two rows are for the same employee and you got what you need

hope this makes sense. If you need more explanation, let me know.

dj