Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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,');
/*****************************************************************
DATA: Expand test data to a more realistic size
*****************************************************************/
INSERT INTO #src WITH(TABLOCK) (ID, ContractID, dt, dowChar, dowInt)
SELECT ID + 2000*x.rn, ContractID + 12*rn, dt, dowChar, dowInt
FROM #src
CROSS APPLY (
SELECT TOP 4000 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS rn
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
) x
GO
SELECT COUNT(*) FROM #src
-- 484121 rows
GO
/*****************************************************************
SOLUTION: Create a function that can split a string of ints into a rowset
*****************************************************************/
IF OBJECT_ID('dbo.f_delimitedIntListSplitter') IS NOT NULL
DROP FUNCTION dbo.f_delimitedIntListSplitter
GO
CREATE function dbo.f_delimitedIntListSplitter (@listString VARCHAR(MAX), @listDelimiter CHAR(1) = ',')
RETURNS @result TABLE (splitListID INT NOT NULL)
AS
BEGIN
DECLARE @xml xml = CAST('<x>'+REPLACE(@listString,@listDelimiter,'</x><x>') + '</x>' as xml)
INSERT INTO @result (splitListID)
SELECT N.value('.','int') AS splitListID
FROM @xml.nodes('x') AS T(N)
RETURN
END
GO
/*****************************************************************
SOLUTION: Add a row number to the data set, in the order we will process the data
We also convert each dowInt into a power of 2 so that we can use a bitmap to
represent which days have been observed in any given grouping
*****************************************************************/
IF OBJECT_ID('tempdb..#srcWithRn') IS NOT NULL
DROP TABLE #srcWithRn
GO
SELECT rn = IDENTITY(INT, 1, 1), ContractId, dt, dowInt,
POWER(2, dowInt) AS dowPower, dowChar
INTO #srcWithRn
FROM #src
ORDER BY ContractId, dt
GO
ALTER TABLE #srcWithRn
ADD PRIMARY KEY (rn)
GO
/*****************************************************************
SOLUTION: Loop over the data, in order by row number. We compute only the list of
row numbers that form the boundary of a new grouping, then output those row numbers into a table
*****************************************************************/
DECLARE @ContractId INT, @RnList VARCHAR(MAX), @NewGrouping BIT = 0, @DowBitmap INT = 0, @startDt DATE
SELECT TOP 1 @ContractId = ContractId, @startDt = dt, @RnList = ',' + CONVERT(VARCHAR(MAX), rn), @DowBitmap = DowPower
FROM #srcWithRn
WHERE rn = 1
SELECT
-- New grouping if new contract, or if we're observing a new day that we did
-- not observe within the first 7 days of the grouping
@NewGrouping = CASE
WHEN ContractId <> @ContractId THEN 1
WHEN DATEDIFF(DAY, @startDt, dt) > 6
AND @DowBitmap & dowPower <> dowPower THEN 1
ELSE 0
END,
@ContractId = ContractId,
-- If this is a newly observed day in an existing grouping, add it to the bitmap
@DowBitmap = CASE WHEN @NewGrouping = 0 THEN @DowBitmap | DowPower ELSE DowPower END,
-- If this is a new grouping, reset the start date of the grouping
@startDt = CASE WHEN @NewGrouping = 0 THEN @startDt ELSE dt END,
-- If this is a new grouping, add this rn to the list of row numbers that delineate the boundary of a new grouping
@RnList = CASE WHEN @NewGrouping = 0 THEN @RnList ELSE @RnList + ',' + CONVERT(VARCHAR(MAX), rn) END
FROM #srcWithRn
WHERE rn >= 2
ORDER BY rn
OPTION (MAXDOP 1)
-- Split the list of grouping boundaries into a table
IF OBJECT_ID('tempdb..#newGroupingRns') IS NOT NULL
DROP TABLE #newGroupingRns
SELECT splitListId AS rn
INTO #newGroupingRns
FROM dbo.f_delimitedIntListSplitter(SUBSTRING(@RnList, 2, 1000000000), DEFAULT)
GO
ALTER TABLE #newGroupingRns
ADD PRIMARY KEY (rn)
GO
/*****************************************************************
SOLUTION: Compute the final groupings by using the boundaries
identified in the loop above
*****************************************************************/
IF OBJECT_ID('tempdb..#finalGroupings') IS NOT NULL
DROP TABLE #finalGroupings
GO
SELECT MIN(s.ContractId) AS ContractId,
MIN(dt) AS StartDT,
MAX(dt) AS EndDT,
COUNT(*) AS DayCount,
CASE WHEN MAX(CASE WHEN dowChar = 'Sun' THEN 1 ELSE 0 END) = 1 THEN 'Sun,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Mon' THEN 1 ELSE 0 END) = 1 THEN 'Mon,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Tue' THEN 1 ELSE 0 END) = 1 THEN 'Tue,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Wed' THEN 1 ELSE 0 END) = 1 THEN 'Wed,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Thu' THEN 1 ELSE 0 END) = 1 THEN 'Thu,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Fri' THEN 1 ELSE 0 END) = 1 THEN 'Fri,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Sat' THEN 1 ELSE 0 END) = 1 THEN 'Sat,' ELSE '' END AS WeekDays
INTO #finalGroupings
FROM #srcWithRn s
CROSS APPLY (
-- For any row, its grouping is the largest boundary row number that occurs at or before this row
SELECT TOP 1 rn AS groupingRn
FROM #newGroupingRns grp
WHERE grp.rn <= s.rn
ORDER BY grp.rn DESC
) g
GROUP BY g.groupingRn
ORDER BY g.groupingRn
GO
/*****************************************************************
SOLUTION: Compare results to optimal solution
*****************************************************************/
SELECT * FROM #finalGroupings WHERE ContractId BETWEEN 1 AND 12 ORDER BY ContractId, StartDT
SELECT * FROM #dst ORDER BY ContractId, StartDT
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment