Search code examples
sql-serverdatebusiness-logic

SQL calculating seniority start date

I have a SQL problem that I cannot solve myself. The problem is I need to calculate Seniority start date in a table of employees.

The table is rather simple, it has 4 columns and a fifth for the calculated seniority.

The business logic is as follows: If an employee starts in the company, stops and starts again within 6 months then his or her seniority is counted from the first start date he or she started in the company. Else is the new company start date.

We need to compare the employees startdate with the employees last stopped date, I guess.

Se the below example: Table of employees

Solution

  • The solution below uses window functions to get the first value.

    Based on a ranking via a cummulative sum.
    Which is based on the month difference between the startdate and the previous end date.

    Not exactly pretty, since it uses 2 sub-queries and 3 window functions for something that seems so simple.
    But it does return those expected results. 

    select 
 EmployeeID, Departement, [Company Start Date], [Company End Date], 
 first_value([Company Start Date]) over (partition by EmployeeID, CummSumRank order by [Company Start Date]) as SeniorityStart
from 
(
    select *, sum(MonthDiffStartVersusPreviousEndToBig) over (partition by EmployeeID order by [Company Start Date]) as CummSumRank
    from 
    (
        select EmployeeID, Departement, [Company Start Date], [Company End Date],
        iif(datediff(month, lag([Company End Date]) over (partition by EmployeeID order by [Company Start Date]), [Company Start Date]) > 6, 1, 0) as MonthDiffStartVersusPreviousEndToBig
        from Employees
    ) as q1
) as q2
order by EmployeeID, [Company Start Date];