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.
Is is actually possible to have multiple rows with the same effdt (2/1/2009) and the same or different empl_status?
Here it is..
sel a.empid as a1, min(b.effdate) as leave_start_date, a.effdate as leave_end_date from
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'
(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;
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.
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.
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.
How to get the merged dataset from your o/p? Pls see me o/p and check. Anyway thanks for yoyr time.
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?
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
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
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.