Data ranges conversion

Transact-SQL
,
1

Hello,

I have employee data like below

Employee_ID Date Previous_FTE FTE
1 2016-03-24 1 0,67
1 2016-04-24 0,67 0,8

I would like to have final out like below

Employee_ID Start_date End Date FTE
1 2016-01-01 2016-03-24 1
1 2016-03-24 2016-04-24 0,67
1 2016-04-24 2016-12-31 0,8

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!

2 
SELECT ca1.*
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Employee_ID ORDER BY Date) AS row_num
    FROM table_name
    WHERE Date >= '20160101'
) AS derived
CROSS APPLY (
    SELECT Employee_ID, Date, FTE
    UNION ALL
    SELECT Employee_ID, '20160101', Previous_FTE
    WHERE row_num = 1 AND Date > '20160101'
) AS ca1
ORDER BY ca1.Employee_ID, ca1.Date
1 Like
3

thank you

4

thanks