Thank you all for your responses.

mister.magoo, your query appears to be giving the correct results.

Another question. Is there a way of merging rows only if they are separated by vacation? So the criteria is the rows must have the same employee, same job, same workload and separated by vacation.

The current data set is:

IDEmployeeJobStartDateEndDateWorkload

1John DoeHSBC2013-08-012013-08-14100

2John DoeVacation2013-08-152013-08-20100

3John DoeHSBC2013-08-212013-08-31100

4John DoeVacation2013-09-202013-09-26100

5John DoeHSBC2013-09-272013-09-30100

6John DoeBarclays2013-10-012013-10-1050

7John DoeVacation2013-10-112013-10-17100

8John DoeBarclays2013-10-182013-10-2450

9John DoeBarclays2013-10-262013-10-3150

10John DoeSantander2013-11-012013-11-0750

11John DoeVacation2013-11-082013-11-15100

12John DoeSantander2013-11-162013-11-2450

13John DoeSantander2013-11-252013-11-3080

The expected results (rows merged are in bold):

IDEmployeeJob StartDate EndDate Workload

1John DoeHSBC 2013-08-01 2013-08-31100

2John DoeVacation2013-08-15 2013-08-20100

4John DoeVacation2013-09-20 2013-09-26100

5John DoeHSBC 2013-09-27 2013-09-30100

6John DoeBarclays2013-10-01 2013-10-2450

7John DoeVacation2013-10-11 2013-10-17100

9John DoeBarclays2013-10-26 2013-10-3150

10John DoeSantander2013-11-01 2013-11-2450

11John DoeVacation2013-11-08 2013-11-15100

13John DoeSantander2013-11-25 2013-11-3080

Please find below the DDL. Any help is appreciated.

set dateformat dmy

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/08/2013', '14/08/2013', 100)

,(2, 'John Doe', 'Vacation', '15/08/2013', '20/08/2013', 100)

,(3, 'John Doe', 'HSBC', '21/08/2013', '31/08/2013', 100)

,(4, 'John Doe', 'Vacation', '20/09/2013', '26/09/2013', 100)

,(5, 'John Doe', 'HSBC', '27/09/2013', '30/09/2013', 100)

,(6, 'John Doe', 'Barclays', '01/10/2013', '10/10/2013', 50)

,(7, 'John Doe', 'Vacation', '11/10/2013', '17/10/2013', 100)

,(8, 'John Doe', 'Barclays', '18/10/2013', '24/10/2013', 50)

,(9, 'John Doe', 'Barclays', '26/10/2013', '31/10/2013', 50)

,(10, 'John Doe', 'Santander', '01/11/2013', '07/11/2013', 50)

,(11, 'John Doe', 'Vacation', '08/11/2013', '15/11/2013', 100)

,(12, 'John Doe', 'Santander', '16/11/2013', '24/11/2013', 50)

,(13, 'John Doe', 'Santander', '25/11/2013', '30/11/2013', 80)

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

select *

from #Something

order by StartDate