DbaStackExchangeGroupedDailySchedule.sql

-- See http://dba.stackexchange.com/questions/136235/group-daily-schedule-into-start-date-end-date-intervals-with-the-list-of-week/136571?noredirect=1#comment257069_136571

/*****************************************************************
 DATA: Create sample data
*****************************************************************/

IF OBJECT_ID('tempdb..#src') IS NOT NULL
	DROP TABLE #src
CREATE TABLE #src (ID int PRIMARY KEY, ContractID int, dt date, dowChar char(3), dowInt int);
INSERT INTO #src (ID, ContractID, dt, dowChar, dowInt) VALUES

-- simple two weeks (without weekend)
(110, 1, '2016-05-02', 'Mon', 2),
(111, 1, '2016-05-03', 'Tue', 3),
(112, 1, '2016-05-04', 'Wed', 4),
(113, 1, '2016-05-05', 'Thu', 5),
(114, 1, '2016-05-06', 'Fri', 6),
(115, 1, '2016-05-09', 'Mon', 2),
(116, 1, '2016-05-10', 'Tue', 3),
(117, 1, '2016-05-11', 'Wed', 4),
(118, 1, '2016-05-12', 'Thu', 5),
(119, 1, '2016-05-13', 'Fri', 6),

-- a partial end of the week, the whole week, partial start of the week (without weekends)
(223, 2, '2016-05-05', 'Thu', 5),
(224, 2, '2016-05-06', 'Fri', 6),
(225, 2, '2016-05-09', 'Mon', 2),
(226, 2, '2016-05-10', 'Tue', 3),
(227, 2, '2016-05-11', 'Wed', 4),
(228, 2, '2016-05-12', 'Thu', 5),
(229, 2, '2016-05-13', 'Fri', 6),
(230, 2, '2016-05-16', 'Mon', 2),
(231, 2, '2016-05-17', 'Tue', 3),

-- only Mon, Wed, Fri are included across two weeks plus partial third week
(310, 3, '2016-05-02', 'Mon', 2),
(311, 3, '2016-05-04', 'Wed', 4),
(314, 3, '2016-05-06', 'Fri', 6),
(315, 3, '2016-05-09', 'Mon', 2),
(317, 3, '2016-05-11', 'Wed', 4),
(319, 3, '2016-05-13', 'Fri', 6),
(330, 3, '2016-05-16', 'Mon', 2),

-- a whole week (without weekend), in the second week Mon is not included
(410, 4, '2016-05-02', 'Mon', 2),
(411, 4, '2016-05-03', 'Tue', 3),
(412, 4, '2016-05-04', 'Wed', 4),
(413, 4, '2016-05-05', 'Thu', 5),
(414, 4, '2016-05-06', 'Fri', 6),
(416, 4, '2016-05-10', 'Tue', 3),
(417, 4, '2016-05-11', 'Wed', 4),
(418, 4, '2016-05-12', 'Thu', 5),
(419, 4, '2016-05-13', 'Fri', 6),

-- three weeks, but without Mon in the second week (no weekends)
(510, 5, '2016-05-02', 'Mon', 2),
(511, 5, '2016-05-03', 'Tue', 3),
(512, 5, '2016-05-04', 'Wed', 4),
(513, 5, '2016-05-05', 'Thu', 5),
(514, 5, '2016-05-06', 'Fri', 6),
(516, 5, '2016-05-10', 'Tue', 3),
(517, 5, '2016-05-11', 'Wed', 4),
(518, 5, '2016-05-12', 'Thu', 5),
(519, 5, '2016-05-13', 'Fri', 6),
(520, 5, '2016-05-16', 'Mon', 2),
(521, 5, '2016-05-17', 'Tue', 3),
(522, 5, '2016-05-18', 'Wed', 4),
(523, 5, '2016-05-19', 'Thu', 5),
(524, 5, '2016-05-20', 'Fri', 6),

-- long gap between two intervals
(623, 6, '2016-05-05', 'Thu', 5),
(624, 6, '2016-05-06', 'Fri', 6),
(625, 6, '2016-05-09', 'Mon', 2),
(626, 6, '2016-05-10', 'Tue', 3),
(627, 6, '2016-05-11', 'Wed', 4),
(628, 6, '2016-05-12', 'Thu', 5),
(629, 6, '2016-05-13', 'Fri', 6),
(630, 6, '2016-05-16', 'Mon', 2),
(631, 6, '2016-05-17', 'Tue', 3),
(645, 6, '2016-06-06', 'Mon', 2),
(646, 6, '2016-06-07', 'Tue', 3),
(647, 6, '2016-06-08', 'Wed', 4),
(648, 6, '2016-06-09', 'Thu', 5),
(649, 6, '2016-06-10', 'Fri', 6),
(655, 6, '2016-06-13', 'Mon', 2),
(656, 6, '2016-06-14', 'Tue', 3),
(657, 6, '2016-06-15', 'Wed', 4),
(658, 6, '2016-06-16', 'Thu', 5),
(659, 6, '2016-06-17', 'Fri', 6),

-- two weeks, no gaps between days at all, even weekends are included
(710, 7, '2016-05-02', 'Mon', 2),
(711, 7, '2016-05-03', 'Tue', 3),
(712, 7, '2016-05-04', 'Wed', 4),
(713, 7, '2016-05-05', 'Thu', 5),
(714, 7, '2016-05-06', 'Fri', 6),
(715, 7, '2016-05-07', 'Sat', 7),
(716, 7, '2016-05-08', 'Sun', 1),
(725, 7, '2016-05-09', 'Mon', 2),
(726, 7, '2016-05-10', 'Tue', 3),
(727, 7, '2016-05-11', 'Wed', 4),
(728, 7, '2016-05-12', 'Thu', 5),
(729, 7, '2016-05-13', 'Fri', 6),

-- no gaps between days at all, even weekends are included, with partial weeks
(805, 8, '2016-04-30', 'Sat', 7),
(806, 8, '2016-05-01', 'Sun', 1),
(810, 8, '2016-05-02', 'Mon', 2),
(811, 8, '2016-05-03', 'Tue', 3),
(812, 8, '2016-05-04', 'Wed', 4),
(813, 8, '2016-05-05', 'Thu', 5),
(814, 8, '2016-05-06', 'Fri', 6),
(815, 8, '2016-05-07', 'Sat', 7),
(816, 8, '2016-05-08', 'Sun', 1),
(825, 8, '2016-05-09', 'Mon', 2),
(826, 8, '2016-05-10', 'Tue', 3),
(827, 8, '2016-05-11', 'Wed', 4),
(828, 8, '2016-05-12', 'Thu', 5),
(829, 8, '2016-05-13', 'Fri', 6),
(830, 8, '2016-05-14', 'Sat', 7),

-- only Mon-Wed included, two weeks plus partial third week
(910, 9, '2016-05-02', 'Mon', 2),
(911, 9, '2016-05-03', 'Tue', 3),
(912, 9, '2016-05-04', 'Wed', 4),
(915, 9, '2016-05-09', 'Mon', 2),
(916, 9, '2016-05-10', 'Tue', 3),
(917, 9, '2016-05-11', 'Wed', 4),
(930, 9, '2016-05-16', 'Mon', 2),
(931, 9, '2016-05-17', 'Tue', 3),

-- only Thu-Sun included, three weeks
(1013,10,'2016-05-05', 'Thu', 5),
(1014,10,'2016-05-06', 'Fri', 6),
(1015,10,'2016-05-07', 'Sat', 7),
(1016,10,'2016-05-08', 'Sun', 1),
(1018,10,'2016-05-12', 'Thu', 5),
(1019,10,'2016-05-13', 'Fri', 6),
(1020,10,'2016-05-14', 'Sat', 7),
(1021,10,'2016-05-15', 'Sun', 1),
(1023,10,'2016-05-19', 'Thu', 5),
(1024,10,'2016-05-20', 'Fri', 6),
(1025,10,'2016-05-21', 'Sat', 7),
(1026,10,'2016-05-22', 'Sun', 1),

-- only Tue for first three weeks, then only Thu for the next three weeks
(1111,11,'2016-05-03', 'Tue', 3),
(1116,11,'2016-05-10', 'Tue', 3),
(1131,11,'2016-05-17', 'Tue', 3),
(1123,11,'2016-05-19', 'Thu', 5),
(1124,11,'2016-05-26', 'Thu', 5),
(1125,11,'2016-06-02', 'Thu', 5);

/*****************************************************************
 DATA: Create optimal results
*****************************************************************/

IF OBJECT_ID('tempdb..#Dst') IS NOT NULL
	DROP TABLE #Dst
CREATE TABLE #Dst (ContractID int, StartDT date, EndDT date, DayCount int, WeekDays varchar(255));
INSERT INTO #Dst (ContractID, StartDT, EndDT, DayCount, WeekDays) VALUES
(1, '2016-05-02', '2016-05-13', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(2, '2016-05-05', '2016-05-17',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(3, '2016-05-02', '2016-05-16',  7, 'Mon,Wed,Fri,'),
(4, '2016-05-02', '2016-05-06',  5, 'Mon,Tue,Wed,Thu,Fri,'),
(4, '2016-05-10', '2016-05-13',  4, 'Tue,Wed,Thu,Fri,'),
(5, '2016-05-02', '2016-05-06',  5, 'Mon,Tue,Wed,Thu,Fri,'),
(5, '2016-05-10', '2016-05-20',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-05-05', '2016-05-17',  9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-06-06', '2016-06-17', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(7, '2016-05-02', '2016-05-13', 12, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(8, '2016-04-30', '2016-05-14', 15, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(9, '2016-05-02', '2016-05-17',  8, 'Mon,Tue,Wed,'),
(10,'2016-05-05', '2016-05-22', 12, 'Sun,Thu,Fri,Sat,'),
(11,'2016-05-03', '2016-05-17',  3, 'Tue,'),
(11,'2016-05-19', '2016-06-02',  3, 'Thu,');

/*****************************************************************
 SOLUTION: Define the contract weeks, along with some metadata
*****************************************************************/

IF OBJECT_ID('tempdb..#contractWeeks') IS NOT NULL
	DROP TABLE #contractWeeks
GO

WITH contractWeeksPivoted AS (
	-- Pivot each week that containts one or more contract days into a single row
	SELECT ContractId,
		DATEADD(DAY, -(dowInt-1), dt) AS WeekStartDate,
		MIN(dt) AS StartDT,
		MAX(dt) AS EndDT,
		COUNT(*) AS DayCount
	FROM #src
	GROUP BY ContractId, DATEADD(DAY, -(dowInt-1), dt)
),
contractWeeksWithWeekDays AS (
	-- Add the comma-delimited list of WeekDays for each week
	SELECT ContractId, WeekStartDate, StartDT, EndDT, DayCount,
		CAST((
			SELECT CAST(s.dowChar AS VARCHAR(MAX)) + ','
			FROM #src s
			WHERE s.ContractId = w.ContractId
				AND s.dt BETWEEN w.weekStartDate AND DATEADD(DAY, 6, w.weekStartDate)
			ORDER BY dowInt ASC
			FOR XML PATH('')
		) AS VARCHAR(28)) AS WeekDays
	FROM contractWeeksPivoted w
),
contractWeekRanks AS (
	-- Add ranks for each week within its contract and within the ContractId and within the ContractId/WeekDays
	SELECT ContractId, WeekStartDate, StartDT, EndDT, DayCount, WeekDays,
		ROW_NUMBER() OVER (PARTITION BY ContractId ORDER BY WeekStartDate) AS ContractWeekRank,
		ROW_NUMBER() OVER (PARTITION BY ContractId, WeekDays ORDER BY WeekStartDate) AS ContractWeekDayRank
	FROM contractWeeksWithWeekDays
),
contractWeeks AS (
	-- Use the ranks to apply a gaps-and-islands methodology for isolating contiguous weeks
	-- both within each ContractId and within each ContractId/Weekdays pair
	SELECT ContractId, WeekStartDate, StartDT, EndDT, DayCount, WeekDays,
		DATEADD(WEEK, -(ContractWeekRank-1), WeekStartDate) AS IslandStartDate,
		DATEADD(WEEK, -(ContractWeekDayRank-1), WeekStartDate) AS IslandWeekdayStartDate
	FROM contractWeekRanks
)
SELECT ContractId, WeekStartDate, StartDT, EndDT, DayCount, WeekDays,
	-- Convert the date representation of the islands into an Id for convenience
	DENSE_RANK() OVER (PARTITION BY ContractId ORDER BY IslandStartDate ASC) AS IslandId,
	DENSE_RANK() OVER (PARTITION BY ContractId ORDER BY IslandWeekdayStartDate ASC) AS WeekdayIslandId,
	-- A bit that will be used during the merging steps below
	CAST(0 AS BIT) AS IsMergedWeek
INTO #contractWeeks
FROM contractWeeks w
GO

CREATE CLUSTERED INDEX islands ON #contractWeeks (ContractId, IslandId, WeekStartDate)
GO

/*****************************************************************
 SOLUTION: Merge adjacent weeks in the same island that are equivalent
*****************************************************************/

WITH WeeksToMerge AS (
	-- Create a single, merged grouping for any weeks part of the same ContractId/WeekDays island
	SELECT ContractId, IslandId, WeekDays, WeekdayIslandId,
		MAX(WeekStartDate) AS WeekStartDateToMergeInto,
		MIN(StartDT) AS NewStartDT,
		MAX(EndDT) AS NewEndDT,
		SUM(DayCount) AS NewDayCount
	FROM #contractWeeks
	GROUP BY ContractId, IslandId, WeekDays, WeekdayIslandId
	HAVING COUNT(*) >= 2
)
-- Update the last week of each grouping to reflect the overall grouping
UPDATE p
SET p.StartDT = m.NewStartDT,
	p.EndDT = m.NewEndDT,
	p.DayCount = m.NewDayCount,
	p.IsMergedWeek = 1
FROM #contractWeeks p
JOIN WeeksToMerge m
	ON m.ContractId = p.ContractId
	AND m.IslandId = p.IslandId
	AND m.WeekStartDateToMergeInto = p.WeekStartDate
GO
-- Delete all other weeks within in each grouping
DELETE p
FROM #contractWeeks p
JOIN #contractWeeks m
	ON m.ContractId = p.ContractId
	AND m.IslandId = p.IslandId
	-- Any week that came before a new merged grouping, but is now contained within that grouping
	AND m.WeekStartDate > p.WeekStartDate
	AND m.StartDT <= p.StartDT
GO

/*****************************************************************
 SOLUTION: Merge any remaining weeks into the next grouping iff
 that next grouping is within the same island and the WeekDays
 for the week are a trailing subset of the WeekDays for the next grouping.
*****************************************************************/

DECLARE @DayCount INT = 1
-- NOTE: We process this in 6 steps, one for each distinct # of DayCounts, to 
-- avoid a problem where we try to simulataneously merge one week into another
-- week that is itself being merged into the following week. E.g., if we had
-- three adjacent weeks that are "Wed,", "Tue,Wed,", and "Mon,Tue,Wed", it would
-- be valid to merge the 1st week into the 2nd. Or to merge the 2nd week into the
-- 3rd. But not to do both simultaneously, and this loop protects us from that.
WHILE (@DayCount <= 6)
BEGIN
	WITH WeeksToMerge AS (
		-- DECLARE @DayCount INT = 2
		SELECT w.ContractId, w.IslandId,
			w.WeekStartDate AS WeekStartDateToMergeInto,
			w_prev.StartDT AS NewStartDT,
			w.DayCount + w_prev.DayCount AS NewDayCount
		FROM #contractWeeks w
		JOIN #contractWeeks w_prev
			ON w_prev.ContractId = w.ContractId
			AND w_prev.IslandId = w.IslandId
			AND w_prev.DayCount = @DayCount
			-- The previous week from the current (potentially merged) week
			AND w_prev.StartDT BETWEEN DATEADD(DAY, -7, w.StartDT) AND DATEADD(DAY, -1, w.StartDT)
			AND w.WeekDays LIKE '%' + w_prev.WeekDays
	)
	UPDATE w
	SET w.StartDT = m.NewStartDT,
		w.DayCount = m.NewDayCount,
		w.IsMergedWeek = 1
	FROM #contractWeeks w
	JOIN WeeksToMerge m
		ON m.ContractId = w.ContractId
		AND m.IslandId = w.IslandId
		AND m.WeekStartDateToMergeInto = w.WeekStartDate

	-- Delete the weeks that have now been merged into the next grouping
	DELETE w_prev
	FROM #contractWeeks w_prev
	JOIN #contractWeeks w_merge
		ON w_merge.ContractId = w_prev.ContractId
		AND w_merge.IslandId = w_prev.IslandId
		-- Comes before the next grouping, but is now contained by that grouping
		AND w_merge.WeekStartDate > w_prev.WeekStartDate
		AND w_merge.StartDT <= w_prev.StartDT

	SET @DayCount = @DayCount + 1
END
GO

/*****************************************************************
 SOLUTION: Merge any remaining weeks into the previous grouping iff
 that previous grouping is within the same island and the WeekDays
 for the week are a leading subset of the WeekDays for the previous grouping.
*****************************************************************/

DECLARE @DayCount INT = 1
-- NOTE: We process this in 6 steps for the same reasons detailed above
WHILE (@DayCount <= 6)
BEGIN
	WITH WeeksToMerge AS (
		SELECT w.ContractId, w.IslandId,
			w.WeekStartDate AS WeekStartDateToMergeInto,
			w_next.EndDT AS NewEndDT,
			w.DayCount + w_next.DayCount AS NewDayCount
		FROM #contractWeeks w
		JOIN #contractWeeks w_next
			ON w_next.ContractId = w.ContractId
			AND w_next.IslandId = w.IslandId
			AND w_next.DayCount = @DayCount
			-- The next week from the current (potentially merged) week
			AND w_next.EndDT BETWEEN DATEADD(DAY, 1, w.EndDT) AND DATEADD(DAY, 7, w.EndDT)
			AND w.WeekDays LIKE w_next.WeekDays + '%'
	)
	UPDATE w
	SET w.EndDT = m.NewEndDT,
		w.DayCount = m.NewDayCount,
		w.IsMergedWeek = 1
	FROM #contractWeeks w
	JOIN WeeksToMerge m
		ON m.ContractId = w.ContractId
		AND m.IslandId = w.IslandId
		AND m.WeekStartDateToMergeInto = w.WeekStartDate

	-- Delete the weeks that have now been merged into the previous grouping
	DELETE w_next
	FROM #contractWeeks w_next
	JOIN #contractWeeks w_merge
		ON w_merge.ContractId = w_next.ContractId
		AND w_merge.IslandId = w_next.IslandId
		-- Comes after the previous grouping, but is now contained by that grouping
		AND w_merge.WeekStartDate < w_next.WeekStartDate
		AND w_merge.EndDT >= w_next.EndDT

	SET @DayCount = @DayCount + 1
END
GO

/*****************************************************************
 SOLUTION: Merge any two adjacent weeks where neither week has yet been merged
 into another week and there is an internal overlap such that the two weeks can
 be combined into a single week.
 E.g., this will handle adjacents week pairs like the following:
	"Fri,Sat," and "Thu,Fri,"
	"Mon,Tue,Wed,Thu," and "Wed,Thu,Sat,"
*****************************************************************/

DECLARE @WeekDayOffset INT = 1
-- In order to identify weeks where the WeekDays match a leading subset not of the overall following week,
-- but only of a trailing subset of the following week, we must probe into each offset.
-- E.g., for a following week of "Sun,Mon,Tue,Wed,", we will probe into "Sun,", "Sun,Mon,", and "Sun,Mon,Tue,"
WHILE (@WeekDayOffset <= 6)
BEGIN
	WITH WeeksToMerge AS (
		-- DECLARE @WeekDayOffset INT = 1
		SELECT w.ContractId, w.IslandId,
			-- Prepend any trailing week days from the first of the two weeks to merge that precede
			-- all week days in the second of the two weeks to merge
			SUBSTRING(w_next.WeekDays, 1, 4*@WeekDayOffset) + w.WeekDays AS NewWeekDays,
			w.WeekStartDate AS WeekStartDateToMergeInto,
			w_next.EndDT AS NewEndDT,
			w.DayCount + w_next.DayCount AS NewDayCount
		FROM #contractWeeks w
		JOIN #contractWeeks w_next
			ON w_next.ContractId = w.ContractId
			AND w_next.IslandId = w.IslandId
			AND w_next.IsMergedWeek = 0
			-- The next week from the current (potentially merged) week
			AND w_next.EndDT BETWEEN DATEADD(DAY, 1, w.EndDT) AND DATEADD(DAY, 7, w.EndDT)
			-- The current week matches a leading subset of a trailing substring of this next week
			AND LEN(w_next.WeekDays) > 4*@WeekDayOffset
			AND w.WeekDays LIKE SUBSTRING(w_next.WeekDays, (4*@WeekDayOffset)+1, 28) + '%'
		-- In both cases, only considered unmerged weeks to ensure the logic of a trailing or leading subset is valid
		WHERE w.IsMergedWeek = 0
	)
	UPDATE w
	SET w.WeekDays = m.NewWeekDays,
		w.EndDT = m.NewEndDT,
		w.DayCount = m.NewDayCount,
		w.IsMergedWeek = 1
	FROM #contractWeeks w
	JOIN WeeksToMerge m
		ON m.ContractId = w.ContractId
		AND m.IslandId = w.IslandId
		AND m.WeekStartDateToMergeInto = w.WeekStartDate

	-- Delete the first week from any pair of weeks that were just merged
	DELETE w_next
	FROM #contractWeeks w_next
	JOIN #contractWeeks w_merge
		ON w_merge.ContractId = w_next.ContractId
		AND w_merge.IslandId = w_next.IslandId
		-- Comes after the previous grouping, but is now contained by that grouping
		AND w_merge.WeekStartDate < w_next.WeekStartDate
		AND w_merge.EndDT >= w_next.EndDT

	SET @WeekDayOffset = @WeekDayOffset + 1
END
GO

/*****************************************************************
 SOLUTION: Handle any unmerged weeks that can be split and two parts,
 with one part being merged into the previous grouping and the second
 part merged into the next grouping
*****************************************************************/

DECLARE @SplitDayOffset INT = 1
-- We probe for a possible split at each offset of the WeekDays.
-- E.g., for "Sun,Mon,Tue,Wed,", potential splits to explore are:
--		"Sun,"			/ "Mon,Tue,Wed,"
--		"Sun,Mon,"		/ "Tue,Wed,"
--		"Sun,Mon,Tue"	/ "Wed,"
WHILE (@SplitDayOffset <= 6)
BEGIN
	-- DECLARE @SplitDayOffset INT = 1
	SELECT w.ContractId, w.IslandId, w.WeekStartDate, w.WeekDays, w.DayCount, w.StartDT, w.EndDT, @SplitDayOffset AS SplitDayOffset
	INTO #tempWeeksToSplitMerge
	FROM #contractWeeks w
	JOIN #contractWeeks w_prev
		ON w_prev.ContractId = w.ContractId
		AND w_prev.IslandId = w.IslandId
		-- The first part of the split matches a leading subset of the previous grouping 
		AND w_prev.EndDT BETWEEN DATEADD(DAY, -7, w.StartDT) AND DATEADD(DAY, -1, w.StartDT)
		AND SUBSTRING(w.WeekDays, 1, 4*@SplitDayOffset) LIKE w_prev.WeekDays + '%'
	JOIN #contractWeeks w_next
		ON w_next.ContractId = w.ContractId
		AND w_next.IslandId = w.IslandId
		-- The second part of the split matches a trailing subset of the next grouping 
		AND w_next.StartDT BETWEEN DATEADD(DAY, 1, w.EndDT) AND DATEADD(DAY, 7, w.EndDT)
		AND SUBSTRING(w.WeekDays, (4*@SplitDayOffset)+1, 28) LIKE '%' + w_next.WeekDays
	WHERE w.IsMergedWeek = 0
		AND LEN(w.WeekDays) > 4*@SplitDayOffset

	-- Merge the first part of the split into the previous week
	UPDATE w
	SET w.EndDT =
			-- Adjust the end of the merged grouping based on the last day that was split/merged into the grouping
			DATEADD(DAY,
				CASE SUBSTRING(sm.WeekDays, 4*(sm.SplitDayOffset-1)+1, 3)
				WHEN 'Sun' THEN 0
				WHEN 'Mon' THEN 1
				WHEN 'Tue' THEN 2
				WHEN 'Wed' THEN 3
				WHEN 'Thu' THEN 4
				WHEN 'Fri' THEN 5 END, 
				sm.WeekStartDate),
		-- Add the # of days in the first part of the split to the DayCount
		w.DayCount = w.DayCount + sm.SplitDayOffset,
		w.IsMergedWeek = 1
	FROM #contractWeeks w
	JOIN #tempWeeksToSplitMerge sm
		ON sm.ContractId = w.ContractId
		AND sm.IslandId = w.islandId
		AND w.EndDT BETWEEN DATEADD(DAY, -7, sm.StartDT) AND DATEADD(DAY, -1, sm.StartDT)

	-- Merge the second part of the split into the next week
	UPDATE w
	SET w.StartDT =
			-- Adjust the start of the merged grouping based on the first day that was split/merged into the grouping
			DATEADD(DAY,
				CASE SUBSTRING(sm.WeekDays, 4*sm.SplitDayOffset+1, 3)
				WHEN 'Sun' THEN 0
				WHEN 'Mon' THEN 1
				WHEN 'Tue' THEN 2
				WHEN 'Wed' THEN 3
				WHEN 'Thu' THEN 4
				WHEN 'Fri' THEN 5 END, 
				sm.WeekStartDate),
		-- Add the # of days in the second part of the split to the DayCount
		DayCount = w.DayCount + (sm.DayCount - sm.SplitDayOffset),
		IsMergedWeek = 1
	FROM #contractWeeks w
	JOIN #tempWeeksToSplitMerge sm
		ON sm.ContractId = w.ContractId
		AND sm.IslandId = w.islandId
		AND w.StartDT BETWEEN DATEADD(DAY, 1, sm.EndDT) AND DATEADD(DAY, 7, sm.EndDT)

	-- Delete any weeks that were split and merged
	DELETE w_split
	FROM #contractWeeks w_split
	JOIN #tempWeeksToSplitMerge sm
		ON sm.ContractId = w_split.ContractId
		AND sm.IslandId = w_split.IslandId
		AND sm.WeekStartDate = w_split.WeekStartDate

	SET @SplitDayOffset = @SplitDayOffset + 1
	IF OBJECT_ID('tempdb..#tempWeeksToSplitMerge') IS NOT NULL
		DROP TABLE #tempWeeksToSplitMerge
END
GO

/*****************************************************************
 SOLUTION: Compare results to optimal solution
*****************************************************************/
SELECT ContractId, StartDT, EndDT, DayCount, WeekDays FROM #contractWeeks ORDER BY ContractId, StartDT
SELECT * FROM #dst ORDER BY ContractId, StartDT
GO