IF OBJECT_ID('tempdb..#Something') is not null

DROP TABLE #Something

CREATE TABLE #Something

(

ID int,

Employee varchar(20),

Job varchar(20),

StartDate datetime,

EndDate datetime,

Workload int

)

INSERT #Something

SELECT *

FROM (VALUES

(1, 'John Doe', 'HSBC', '01/01/2013', '31/12/2013', 100)

,(2, 'John Doe', 'Vacation', '17/06/2013', '21/06/2013', 100)

,(3, 'John Doe', 'HSBC', '01/01/2014', '31/12/2014', 100)

,(4, 'John Doe', 'Vacation', '19/08/2013', '23/08/2013', 100)

,(5, 'John Doe', 'Barclays', '01/01/2014', '31/01/2014', 50)

,(6, 'John Doe', 'Barclays', '01/01/2015', '31/01/2015', 50)

,(7, 'John Doe', 'Santander', '06/01/2014', '25/01/2014', 50)

,(8, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 80)

,(9, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 50)

,(10, 'John Doe', 'Vacation', '13/01/2014', '17/01/2014', 100)

) x(a,b,c,d,e,f)

SELECT

ROW_NUMBER() OVER (ORDER BY StartDate, EndDate) AS ID,

r.*

FROM

(

SELECT DISTINCT

Employee,

Job,

MIN(StartDate) OVER (PARTITION BY Employee, Job, Workload) AS StartDate,

MIN(EndDate) OVER (PARTITION BY Employee, Job, Workload) AS EndDate,

Workload

FROM

#Something

WHERE

Job <> 'vacation'

) r

IDEmployeeJobStartDateEndDateWorkload

1John DoeHSBC2013-01-01 00:00:00.0002013-12-31 00:00:00.000100

2John DoeBarclays2014-01-01 00:00:00.0002014-01-31 00:00:00.00050

3John DoeSantander2014-01-06 00:00:00.0002014-01-25 00:00:00.00050

4John DoeSantander2014-03-05 00:00:00.0002014-07-17 00:00:00.00080