I'm working with Oracle and have sample data to show you regarding my question (sample data is below).
I'm trying to figure out how to derive years of service for employees that have terminated and returned several times (up to 6 times). The business rules are that if someone leaves and returns before 365 days, then we bridge their service -- otherwise we don't. Bridging is determined by the "Yes" and "No" values in the BRIDGE fields.
My sample data below: The HIRE, TERM and BRIDGE fields go up to 3 levels but it can at times go all the way up to 6 levels deep for some employees.
╔════════╦════════════╦════════════╦══════════╦════════════╦═══════════╦══════════╦═══════════╗
║ EMPLID ║ HIRE_1 ║ TERM_1 ║ BRIDGE_1 ║ HIRE_2 ║ TERM_2 ║ BRIDGE_2 ║ HIRE_3 ║
╠════════╬════════════╬════════════╬══════════╬════════════╬═══════════╬══════════╬═══════════╣
║ 1001 ║ 9/22/2011 ║ 12/9/2011 ║ Yes ║ 7/1/2012 ║ 7/2/2013 ║ Yes ║ 5/3/2014 ║
║ 1002 ║ 3/29/1999 ║ 6/30/1999 ║ Yes ║ 1/24/2000 ║ 3/29/2004 ║ No ║ 11/5/2007 ║
║ 1003 ║ 8/16/2009 ║ 12/31/2009 ║ Yes ║ 1/1/2010 ║ 5/15/2012 ║ Yes ║ 9/5/2012 ║
║ 1004 ║ 7/23/2007 ║ 8/21/2009 ║ Yes ║ 3/1/2010 ║ 2/9/2011 ║ Yes ║ 4/25/2011 ║
║ 1005 ║ 5/11/2010 ║ 8/31/2010 ║ Yes ║ 7/1/2011 ║ 5/5/2012 ║ Yes ║ 9/6/2012 ║
║ 1006 ║ 1/17/2009 ║ 5/8/2009 ║ Yes ║ 12/8/2009 ║ 5/4/2010 ║ Yes ║ 1/18/2011 ║
║ 1007 ║ 9/20/2004 ║ 5/4/2007 ║ No ║ 11/15/2010 ║ 1/21/2011 ║ Yes ║ 2/16/2011 ║
║ 1008 ║ 6/22/2009 ║ 8/29/2009 ║ No ║ 5/9/2011 ║ 4/13/2012 ║ Yes ║ 5/21/2012 ║
║ 1009 ║ 10/13/2008 ║ 5/9/2009 ║ Yes ║ 8/24/2009 ║ 5/25/2010 ║ Yes ║ 8/10/2010 ║
║ 1010 ║ 8/13/2008 ║ 5/7/2010 ║ Yes ║ 1/4/2011 ║ 5/31/2011 ║ Yes ║ 8/15/2011 ║
║ 5011 ║ 7/3/1985 ║ 10/24/2000 ║ No ║ 7/19/2010 ║ ║ No ║ ║
╚════════╩════════════╩════════════╩══════════╩════════════╩═══════════╩══════════╩═══════════╝
For the last entry, 5011, the employee was rehired on 7/19/2010 and hasn't left. So the calculation should be a little less than 5 years (4.xx). Because the prior termination date between the next hire date was more than 365 days, bridge_1
is "No". So this resets years recognized for service. Otherwise if the employee was rehired less than 365 days from the prior term, then the bridge_1
would be yes.
I'm looking for the most recent period of bridged employment, so if there are two periods separated by a gap of more than a year (as for employee 5011) then I only want the most recent, not the total of all the separate contiguous (bridged) periods.