Been trying to solve this for couple days (SQL Server 2014) but it is beyond my starting\limited knowledge. It is a practical scenario of attempting to calculate a running total of our employees seniority based upon eligibility periods (Basically Benefit Retention Periods that if exceeded, reset seniority) that occur during LayOff or Leave of Absence. Problem is that eligibility Length is based upon Seniority in 3 different levels. Therefore as I attempt to add the running total it needs to account for when there is layoffs\leave that exceeds the allowed eligibility based on 3 levels of seniority.
I have combined my tables to a format where Employees with only a HireDate (No employment history entries) have been included (like 129) and gap eligibility and seniority time are calculated. Columns are as follows:
RN = Row Number
ID_P = ID_Personnel
ID_Status = Status type - 1 for employed and 2 for leave\laid off
Srt1-3 = (Business Rule) Seniority Level Break Points in Years
E1-3 = (Business Rule) Eligibility period for each seniority level in Days
SCDate = Status Change Date, new date entered for each change from employed to laid off\leave
EPDays = Eligibility Gap Duration (Days) - Gap during leave\laid off
SYrs = Seniority Gap Duration (Yrs) - Gap during Employement
RTS (Want) = Want Seniority Rolling Total reset-able by business rules above
This produces the following data and I've added manually values for RTS column:
RN, ID_P, ID_S, Srt1, Srt2, Srt3, E1, E2, E3, SCDate , EPDays, SYrs, RTS (want)
1 , 129 , 1 , 0 , 5 , 10 , 30, 60, 90, 2012-06-12 , 0 , 3.41, 3.41
1 , 130 , 2 , 0 , 5 , 10 , 30, 60, 90, 2001-01-01 , 29 , 1.00, 1.00 (Continue RT due to 29 < 30 for Seniority less 5 years)
2 , 130 , 1 , 0 , 5 , 10 , 30, 60, 90, 2001-01-30 , 0 , 5.00, 6.00
3 , 130 , 2 , 0 , 5 , 10 , 30, 60, 90, 2006-01-30 , 61 , 0.00, 0 (reset RT due to 6 yrs seniority = 60 < 61)
4 , 130 , 1 , 0 , 5 , 10 , 30, 60, 90, 2006-04-01 , 0 , 9.60, 9.60
5 , 130 , 2 , 0 , 5 , 10 , 30, 60, 90, 2015-11-08 , 1 , 0.00, 9.60
1 , 131 , 2 , 0 , 5 , 10 , 30, 60, 90, 2000-02-01 , 28 , 0.08, 0.08 (Continue due to 28 < 30 for Srt1)
2 , 131 , 1 , 0 , 5 , 10 , 30, 60, 90, 2000-02-29 , 0 , 10.00,10.08
3 , 131 , 2 , 0 , 5 , 10 , 30, 60, 90, 2010-03-01 , 92 , 0.00, 0.00 (Reset due to 10 yrs seniority and 92>90)
4 , 131 , 1 , 0 , 5 , 10 , 30, 60, 90, 2010-06-01 , 0 , 5.44, 5.44
I've tried several variations of Case, window functions, lead, lag, etc. all running into same issue of not being able to use running total as a reference in order to compare it to business rules.
I'm not opposed to making this a stored procedure either...
