This appears to work:

SELECT

ID = ROW_NUMBER() OVER(ORDER BY MIN(DateRange)),

Employee, Job, [Workload],

StartDate = MIN(DateRange),

EndDate = MAX(DateRange)

FROM (

SELECT ID, Employee, Job, [Workload], DateRange,

Grouper = DENSE_RANK() over (order by Employee, daterange)

- DENSE_RANK() over (order by ID, Employee, daterange)

FROM (

SELECT

so.ID,

so.Employee,

Job = so.Job,

so.[Workload],

c.DateRange,

rn = DENSE_RANK() OVER(PARTITION BY c.DateRange ORDER BY CASE WHEN so.Job = 'Vacation' THEN 0 ELSE 1 END)

FROM #Something so

CROSS APPLY (SELECT RangeStart = MIN(StartDate), RangeEnd = MAX(EndDate) FROM #Something si) r

CROSS APPLY dbo.IF_Calendar (r.RangeStart,r.RangeEnd,'monday') c

WHERE c.DateRange BETWEEN so.StartDate AND so.EndDate

) d

WHERE rn = 1

) q

GROUP BY ID, Employee, Job, [Workload], Grouper

Here's the function definition:

CREATE FUNCTION [dbo].[IF_Calendar]

(

@StartDate DATE,

@EndDate DATE,

@FirstWeekDay VARCHAR(10)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

(

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows

E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max

iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive

SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))

rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM E3

)

-- Do some date arithmetic

SELECT

a.DateRange,

c.[Year],

c.[Month],

c.[DayOfMonth],

c.AbsWeekno,

c.[DayName],

d.Holiday

FROM iTally

CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a

CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)

) b (FirstWeekDay, FirstWeekdayOffset)

CROSS APPLY (

SELECT

[Year] = YEAR(a.DateRange),

[Month] = MONTH(a.DateRange),

[DayOfMonth] = DAY(a.DateRange),

AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,

[DayName]= DATENAME(weekday,a.DateRange)

) c

CROSS APPLY (

SELECT Holiday = CASE

WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'

WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'

WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'

WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'

WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'

WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'

ELSE NULL END

) d

WHERE b.FirstWeekDay = @FirstWeekDay

AND @EndDate IS NOT NULL

)

GO

Which you could easily replace with an inline tally table.