Need to calculate using LEAD, LAG function

Database
Enthusiast

Need to calculate using LEAD, LAG function

Dear Experts,

I am stuck with the below issues. Pls help.

The below example shows the leave records for an employee id 31750. We need to calculate the leave duration. Here EMPL_STATUS='LEAVE' indicates leave and 'ACTIVE' indicates the active status of the employee. If you see the data, there are 3 leave periods showing in the below table which we need to capture by writing SQL.

If you see the data from bottom to top,

leave starts from 1/1/2009 (status='LEAVE') and ends on 1/23/2009 (status='ACTIVE').

So the 1st leave period is 1/1/2009 to 1/23/2009.

2nd leave period starts from 2/1/2009 (status='LEAVE') and ends on 5/2/2010 (status='ACTIVE')

so the leave duration is 2/1/2009 to 5/2/2010

3rd leave period starts from 7/1/2011 (status='LEAVE') and ends on 11/1/2011 (status='ACTIVE')

So the leave duration is 7/1/2011 to 11/1/2011.

So the output record should look like

EMPLID   LEAVE_START_DATE   LEAVE_END_DATE 

31750      1/1/2009        1/23/2009

31750      2/1/2009        5/2/2010

31750      7/1/2011        11/1/2011

The base data is shown in the below table.






EMPLID EFFDT EMPL_STATUS
31750 11/1/2011 ACTIVE
31750 7/1/2011 LEAVE
31750 8/31/2010 LEAVE
31750 7/1/2010 LEAVE
31750 5/2/2010 ACTIVE
31750 4/1/2010 LEAVE
31750 6/1/2009 LEAVE
31750 2/1/2009 LEAVE
31750 2/1/2009 ACTIVE
31750 2/1/2009 ACTIVE
31750 1/23/2009 ACTIVE
31750 1/6/2009 LEAVE
31750 1/1/2009 LEAVE
Tags (1)
  • Tags:
8 REPLIES
Senior Apprentice

Re: Need to calculate using LEAD, LAG function

Is is actually possible to have multiple rows with the same effdt (2/1/2009) and the same or different empl_status?

Dieter

Enthusiast

Re: Need to calculate using LEAD, LAG function

Here it is..

sel a.empid as a1, min(b.effdate) as leave_start_date, a.effdate as leave_end_date from

    (sel empid,effdate,

         coalesce(

                min(effdate)over(partition by empid order by effdate rows between 1 preceding and 1 preceding)

               ,cast('1900-01-01'as date)) as n1

         from mkdate where emp_stat='ACTIVE'

     )a

left join

(sel empid,effdate from mkdate where emp_stat='LEAVE')b

on a.empid = b.empid and  a.effdate  >= b.effdate and a.n1 < b.effdate

group by 1,3

order by 1,3,2;

Regards,

Mohan K

Enthusiast

Re: Need to calculate using LEAD, LAG function

Sel emplid,effdt,empl_status, coalesce(mdiff(est,1,rno),-1) cnn from

(sel emplid,effdt,empl_status, case empl_status when 'active' then 2 else 1 end est, rank(effdt asc,empl_status) rno from tab1)t

qualify cnn in(-1,1)

my answerset would be like this

31,750    1/1/2009      LEAVE

31,750    1/23/2009    ACTIVE

31,750    2/1/2009      LEAVE

31,750    2/1/2009      ACTIVE

31,750    6/1/2009      LEAVE

31,750    5/2/2010      ACTIVE

31,750    7/1/2010      LEAVE

31,750    11/1/2011    ACTIVE

use the above query to seperate the EFFDT column as per your requirement.

Enthusiast

Re: Need to calculate using LEAD, LAG function

Yes Dnoeth, It is possible ho have multiple rows for same effdate . In that case latest row will be picked based on SEQUNCE column which has sequnce values.

Enthusiast

Re: Need to calculate using LEAD, LAG function

Hi Mohan,

your o/p is below. Not matching though

 a1 leave_start_date leave_end_date

1 000031750  01/01/2009

2 000031750  06/01/2009

3 000031750  23/01/2009

4 000031750 01/02/2009 01/02/2009

5 000031750 01/06/2009 02/05/2010

6 000031750 01/07/2010 01/11/2011

See the expected o/p and this o/p. Anyway thanks for your precious time.

Hi Mathuram,

How to get the merged dataset from your o/p? Pls see me o/p and check. Anyway thanks for yoyr time.

Enthusiast

Re: Need to calculate using LEAD, LAG function

Hi Arbiswas,

Yes this won't  match with your result set which given in the initial post because of the same date is having multiple status(2/1/2009). However executing the query from my end which gives the result as like below..

    31750    01/01/2009    01/23/2009

    31750    02/01/2009    02/01/2009

    31750    06/01/2009    05/02/2010

    31750    07/01/2010    11/01/2011

could you please help me to understand how you are getting the 6 rows as output for the given ID instead of 4?

Regards,

Mohan K

Senior Apprentice

Re: Need to calculate using LEAD, LAG function

Your expected output is probably wrong, the 3rd leave start on 7/1/2010 according to your date not on 7/1/2011.

This combines the rows from the first "leave" up to the next "active" row:

SELECT emplid, MIN(effdt) AS LEAVE_START_DATE, x AS LEAVE_END_DATE 
FROM
(
SELECT t.*,
MIN(CASE WHEN empl_status = 'active' THEN effdt END)
OVER (PARTITION BY emplid
ORDER BY effdt, sequence
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS x
FROM dropme AS t
QUALIFY empl_status = 'LEAVE'
) AS dt
GROUP BY 1,x

Dieter

Senior Apprentice

Re: Need to calculate using LEAD, LAG function

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING is similar to ROWS UNBOUNDED PRECEDING (= ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), but ROWS UNBOUNDED FOLLOWING is syntactically not allowed.

Dieter