SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Conditional lag/lead function

Krishna Devi VinayakaSep 8 2015 — edited Sep 8 2015

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

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 6 2015

Added on Sep 8 2015

8 comments

6,530 views