Convert into multiple data ranges

Question

I have employee data link below

This is one example in the file a have multiple lines. Initial input can have more than 2 lines for each employee but convert logic will be the same.

Thank you for help!

Answer 1

After setting up some test data:

DECLARE @EmployeeUtil TABLE (
    Employee_ID INT,
    [Date] DATE,
    Previous_FTE DECIMAL(3,2),
    FTE DECIMAL(3,2)
)

INSERT INTO @EmployeeUtil
SELECT 1, '2016-03-24', 1, 0.67
UNION
SELECT 1, '2016-04-24', 0.67, 0.8
UNION
SELECT 1, '2016-06-01', 0.8, 1
UNION
SELECT 2, '2016-01-01', 1, 0.5
UNION
SELECT 2, '2016-02-03', 0.5, 0.6
UNION
SELECT 2, '2016-03-25', 0.6, 0.8
UNION
SELECT 2, '2016-04-20', 0.8, 0.25
UNION
SELECT 2, '2016-05-01', 0.25, 0.4
UNION
SELECT 2, '2016-06-01', 0.4, 0.8
UNION
SELECT 2, '2016-07-01', 0.8, 1

I would use Window Functions to achieve the desired result:

    SELECT  Employee_ID,
            [Date] AS [Start_date],
            LEAD([Date]) OVER (PARTITION BY Employee_ID ORDER BY [DATE]) AS [End Date],
            FTE
    FROM @EmployeeUtil 
UNION
    SELECT  e1.Employee_ID,
            NULL AS [Start_date],
            FIRST_VALUE(e1.[Date]) OVER (PARTITION BY e1.Employee_ID ORDER BY e1.[DATE]) AS [End Date],
            FIRST_VALUE(e1.Previous_FTE) OVER (PARTITION BY e1.Employee_ID ORDER BY e1.[DATE]) AS FTE
    FROM @EmployeeUtil e1
ORDER BY 1, 2, 3

The SELECT in the first part of the UNION fetches all of the ranges of dates with the appropriate start and end dates and FTE values for that date range, except for the unbounded period prior to the earliest date recorded for each employee.

The SELECT in the second part of the UNION deals with this, creating an extra row in the results for each Employee to show their FTE value prior to the earliest date.