SQL & PL/SQL

Hi,

Is it possible to add a condition in lag function.

I have following table

============================================================

EMPID                 DATE                 UNPAID          OFF           HOLIDAY

============================================================

1                         21/03/2015             YES              

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

1                         22/03/2015                                  YES                   

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

1                         23/03/2015             YES                                         

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

1                         24/03/2015                                                               YES            

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

1                         25/03/2015             YES              

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

Here a rule is that if off/holiday comes between two unpaid days, the employee is not eligible for that off or holiday and would be considered as unpaid.

Hence the required output is

============================================================

EMPID                 DATE                 UNPAID          OFF           HOLIDAY

============================================================

1                         21/03/2015             YES              

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

1                         22/03/2015             YES                   

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

1                         23/03/2015             YES                                         

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

1                         24/03/2015             YES            

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

1                         25/03/2015             YES              

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

I used following query to get the above output

select empid,

          prdate,          

          case when nvl(lag(unpaid) over (partition by empid order by prdate),0)=1

                       and  nvl(lead(unpaid) over (partition by empid order by prdate),0)=1

       then 0 else off end as off  from test

But consider he following case.

============================================================

EMPID                 DATE                 UNPAID          OFF           HOLIDAY

============================================================

1                         21/03/2015             YES              

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

1                         22/03/2015                                  YES                   

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

1                         23/03/2015                                                       YES

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

1                         24/03/2015             YES              

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

Here if the last working day preceding the off/holiday is unpaid day and if the first working day following an off/holiday is unpaid, then the employee is not eligible for the off/holiday.

Can i do this in a query?

Required output is

============================================================

EMPID                 DATE                 UNPAID          OFF           HOLIDAY

============================================================

1                         21/03/2015             YES              

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

1                         22/03/2015             YES                   

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

1                         23/03/2015              YES

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

1                         24/03/2015             YES              

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

DATA

======

with test as

(

select 1 empid, to_date('21/03/2015','dd/mm/yyyy') prdate,1 unpaid,0 off,0 holiday from dual union all

select 1 empid, to_date('22/03/2015','dd/mm/yyyy') prdate,0 unpaid,1,0 from dual union all

select 1 empid, to_date('23/03/2015','dd/mm/yyyy') prdate,0 unpaid,0,1 from dual union all

select 1 empid, to_date('24/03/2015','dd/mm/yyyy') prdate,1 unpaid,0,0 from dual

)

I use oracle 10 g

Please help