· 6 years ago · Aug 14, 2019, 11:16 PM
1+----+------------+------------+---------+--------+
2| ID | ContractID | dt | dowChar | dowInt |
3+----+------------+------------+---------+--------+
4| 10 | 1 | 2016-05-02 | Mon | 2 |
5| 11 | 1 | 2016-05-03 | Tue | 3 |
6| 12 | 1 | 2016-05-04 | Wed | 4 |
7| 13 | 1 | 2016-05-05 | Thu | 5 |
8| 14 | 1 | 2016-05-06 | Fri | 6 |
9| 15 | 1 | 2016-05-09 | Mon | 2 |
10| 16 | 1 | 2016-05-10 | Tue | 3 |
11| 17 | 1 | 2016-05-11 | Wed | 4 |
12| 18 | 1 | 2016-05-12 | Thu | 5 |
13| 19 | 1 | 2016-05-13 | Fri | 6 |
14+----+------------+------------+---------+--------+
15
16+------------+------------+------------+----------+----------------------+
17| ContractID | StartDT | EndDT | DayCount | WeekDays |
18+------------+------------+------------+----------+----------------------+
19| 1 | 2016-05-02 | 2016-05-13 | 10 | Mon,Tue,Wed,Thu,Fri, |
20+------------+------------+------------+----------+----------------------+
21
22+-----+------------+------------+---------+--------+
23| ID | ContractID | dt | dowChar | dowInt |
24+-----+------------+------------+---------+--------+
25| 223 | 2 | 2016-05-05 | Thu | 5 |
26| 224 | 2 | 2016-05-06 | Fri | 6 |
27| 225 | 2 | 2016-05-09 | Mon | 2 |
28| 226 | 2 | 2016-05-10 | Tue | 3 |
29| 227 | 2 | 2016-05-11 | Wed | 4 |
30| 228 | 2 | 2016-05-12 | Thu | 5 |
31| 229 | 2 | 2016-05-13 | Fri | 6 |
32| 230 | 2 | 2016-05-16 | Mon | 2 |
33| 231 | 2 | 2016-05-17 | Tue | 3 |
34+-----+------------+------------+---------+--------+
35
36+------------+------------+------------+----------+----------------------+
37| ContractID | StartDT | EndDT | DayCount | WeekDays |
38+------------+------------+------------+----------+----------------------+
39| 2 | 2016-05-05 | 2016-05-17 | 9 | Mon,Tue,Wed,Thu,Fri, |
40+------------+------------+------------+----------+----------------------+
41
42+------------+------------+------------+----------+----------------------+
43| ContractID | StartDT | EndDT | DayCount | WeekDays |
44+------------+------------+------------+----------+----------------------+
45| 2 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
46| 2 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
47| 2 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
48+------------+------------+------------+----------+----------------------+
49
50-- @Src is sample data
51-- @Dst is expected result
52
53DECLARE @Src TABLE (ID int PRIMARY KEY, ContractID int, dt date, dowChar char(3), dowInt int);
54INSERT INTO @Src (ID, ContractID, dt, dowChar, dowInt) VALUES
55
56-- simple two weeks (without weekend)
57(110, 1, '2016-05-02', 'Mon', 2),
58(111, 1, '2016-05-03', 'Tue', 3),
59(112, 1, '2016-05-04', 'Wed', 4),
60(113, 1, '2016-05-05', 'Thu', 5),
61(114, 1, '2016-05-06', 'Fri', 6),
62(115, 1, '2016-05-09', 'Mon', 2),
63(116, 1, '2016-05-10', 'Tue', 3),
64(117, 1, '2016-05-11', 'Wed', 4),
65(118, 1, '2016-05-12', 'Thu', 5),
66(119, 1, '2016-05-13', 'Fri', 6),
67
68-- a partial end of the week, the whole week, partial start of the week (without weekends)
69(223, 2, '2016-05-05', 'Thu', 5),
70(224, 2, '2016-05-06', 'Fri', 6),
71(225, 2, '2016-05-09', 'Mon', 2),
72(226, 2, '2016-05-10', 'Tue', 3),
73(227, 2, '2016-05-11', 'Wed', 4),
74(228, 2, '2016-05-12', 'Thu', 5),
75(229, 2, '2016-05-13', 'Fri', 6),
76(230, 2, '2016-05-16', 'Mon', 2),
77(231, 2, '2016-05-17', 'Tue', 3),
78
79-- only Mon, Wed, Fri are included across two weeks plus partial third week
80(310, 3, '2016-05-02', 'Mon', 2),
81(311, 3, '2016-05-04', 'Wed', 4),
82(314, 3, '2016-05-06', 'Fri', 6),
83(315, 3, '2016-05-09', 'Mon', 2),
84(317, 3, '2016-05-11', 'Wed', 4),
85(319, 3, '2016-05-13', 'Fri', 6),
86(330, 3, '2016-05-16', 'Mon', 2),
87
88-- a whole week (without weekend), in the second week Mon is not included
89(410, 4, '2016-05-02', 'Mon', 2),
90(411, 4, '2016-05-03', 'Tue', 3),
91(412, 4, '2016-05-04', 'Wed', 4),
92(413, 4, '2016-05-05', 'Thu', 5),
93(414, 4, '2016-05-06', 'Fri', 6),
94(416, 4, '2016-05-10', 'Tue', 3),
95(417, 4, '2016-05-11', 'Wed', 4),
96(418, 4, '2016-05-12', 'Thu', 5),
97(419, 4, '2016-05-13', 'Fri', 6),
98
99-- three weeks, but without Mon in the second week (no weekends)
100(510, 5, '2016-05-02', 'Mon', 2),
101(511, 5, '2016-05-03', 'Tue', 3),
102(512, 5, '2016-05-04', 'Wed', 4),
103(513, 5, '2016-05-05', 'Thu', 5),
104(514, 5, '2016-05-06', 'Fri', 6),
105(516, 5, '2016-05-10', 'Tue', 3),
106(517, 5, '2016-05-11', 'Wed', 4),
107(518, 5, '2016-05-12', 'Thu', 5),
108(519, 5, '2016-05-13', 'Fri', 6),
109(520, 5, '2016-05-16', 'Mon', 2),
110(521, 5, '2016-05-17', 'Tue', 3),
111(522, 5, '2016-05-18', 'Wed', 4),
112(523, 5, '2016-05-19', 'Thu', 5),
113(524, 5, '2016-05-20', 'Fri', 6),
114
115-- long gap between two intervals
116(623, 6, '2016-05-05', 'Thu', 5),
117(624, 6, '2016-05-06', 'Fri', 6),
118(625, 6, '2016-05-09', 'Mon', 2),
119(626, 6, '2016-05-10', 'Tue', 3),
120(627, 6, '2016-05-11', 'Wed', 4),
121(628, 6, '2016-05-12', 'Thu', 5),
122(629, 6, '2016-05-13', 'Fri', 6),
123(630, 6, '2016-05-16', 'Mon', 2),
124(631, 6, '2016-05-17', 'Tue', 3),
125(645, 6, '2016-06-06', 'Mon', 2),
126(646, 6, '2016-06-07', 'Tue', 3),
127(647, 6, '2016-06-08', 'Wed', 4),
128(648, 6, '2016-06-09', 'Thu', 5),
129(649, 6, '2016-06-10', 'Fri', 6),
130(655, 6, '2016-06-13', 'Mon', 2),
131(656, 6, '2016-06-14', 'Tue', 3),
132(657, 6, '2016-06-15', 'Wed', 4),
133(658, 6, '2016-06-16', 'Thu', 5),
134(659, 6, '2016-06-17', 'Fri', 6),
135
136-- two weeks, no gaps between days at all, even weekends are included
137(710, 7, '2016-05-02', 'Mon', 2),
138(711, 7, '2016-05-03', 'Tue', 3),
139(712, 7, '2016-05-04', 'Wed', 4),
140(713, 7, '2016-05-05', 'Thu', 5),
141(714, 7, '2016-05-06', 'Fri', 6),
142(715, 7, '2016-05-07', 'Sat', 7),
143(716, 7, '2016-05-08', 'Sun', 1),
144(725, 7, '2016-05-09', 'Mon', 2),
145(726, 7, '2016-05-10', 'Tue', 3),
146(727, 7, '2016-05-11', 'Wed', 4),
147(728, 7, '2016-05-12', 'Thu', 5),
148(729, 7, '2016-05-13', 'Fri', 6),
149
150-- no gaps between days at all, even weekends are included, with partial weeks
151(805, 8, '2016-04-30', 'Sat', 7),
152(806, 8, '2016-05-01', 'Sun', 1),
153(810, 8, '2016-05-02', 'Mon', 2),
154(811, 8, '2016-05-03', 'Tue', 3),
155(812, 8, '2016-05-04', 'Wed', 4),
156(813, 8, '2016-05-05', 'Thu', 5),
157(814, 8, '2016-05-06', 'Fri', 6),
158(815, 8, '2016-05-07', 'Sat', 7),
159(816, 8, '2016-05-08', 'Sun', 1),
160(825, 8, '2016-05-09', 'Mon', 2),
161(826, 8, '2016-05-10', 'Tue', 3),
162(827, 8, '2016-05-11', 'Wed', 4),
163(828, 8, '2016-05-12', 'Thu', 5),
164(829, 8, '2016-05-13', 'Fri', 6),
165(830, 8, '2016-05-14', 'Sat', 7),
166
167-- only Mon-Wed included, two weeks plus partial third week
168(910, 9, '2016-05-02', 'Mon', 2),
169(911, 9, '2016-05-03', 'Tue', 3),
170(912, 9, '2016-05-04', 'Wed', 4),
171(915, 9, '2016-05-09', 'Mon', 2),
172(916, 9, '2016-05-10', 'Tue', 3),
173(917, 9, '2016-05-11', 'Wed', 4),
174(930, 9, '2016-05-16', 'Mon', 2),
175(931, 9, '2016-05-17', 'Tue', 3),
176
177-- only Thu-Sun included, three weeks
178(1013,10,'2016-05-05', 'Thu', 5),
179(1014,10,'2016-05-06', 'Fri', 6),
180(1015,10,'2016-05-07', 'Sat', 7),
181(1016,10,'2016-05-08', 'Sun', 1),
182(1018,10,'2016-05-12', 'Thu', 5),
183(1019,10,'2016-05-13', 'Fri', 6),
184(1020,10,'2016-05-14', 'Sat', 7),
185(1021,10,'2016-05-15', 'Sun', 1),
186(1023,10,'2016-05-19', 'Thu', 5),
187(1024,10,'2016-05-20', 'Fri', 6),
188(1025,10,'2016-05-21', 'Sat', 7),
189(1026,10,'2016-05-22', 'Sun', 1),
190
191-- only Tue for first three weeks, then only Thu for the next three weeks
192(1111,11,'2016-05-03', 'Tue', 3),
193(1116,11,'2016-05-10', 'Tue', 3),
194(1131,11,'2016-05-17', 'Tue', 3),
195(1123,11,'2016-05-19', 'Thu', 5),
196(1124,11,'2016-05-26', 'Thu', 5),
197(1125,11,'2016-06-02', 'Thu', 5),
198
199-- one week, then one week gap, then one week
200(1210,12,'2016-05-02', 'Mon', 2),
201(1211,12,'2016-05-03', 'Tue', 3),
202(1212,12,'2016-05-04', 'Wed', 4),
203(1213,12,'2016-05-05', 'Thu', 5),
204(1214,12,'2016-05-06', 'Fri', 6),
205(1215,12,'2016-05-16', 'Mon', 2),
206(1216,12,'2016-05-17', 'Tue', 3),
207(1217,12,'2016-05-18', 'Wed', 4),
208(1218,12,'2016-05-19', 'Thu', 5),
209(1219,12,'2016-05-20', 'Fri', 6);
210
211SELECT ID, ContractID, dt, dowChar, dowInt
212FROM @Src
213ORDER BY ContractID, dt;
214
215
216DECLARE @Dst TABLE (ContractID int, StartDT date, EndDT date, DayCount int, WeekDays varchar(255));
217INSERT INTO @Dst (ContractID, StartDT, EndDT, DayCount, WeekDays) VALUES
218(1, '2016-05-02', '2016-05-13', 10, 'Mon,Tue,Wed,Thu,Fri,'),
219(2, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
220(3, '2016-05-02', '2016-05-16', 7, 'Mon,Wed,Fri,'),
221(4, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
222(4, '2016-05-10', '2016-05-13', 4, 'Tue,Wed,Thu,Fri,'),
223(5, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
224(5, '2016-05-10', '2016-05-20', 9, 'Mon,Tue,Wed,Thu,Fri,'),
225(6, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
226(6, '2016-06-06', '2016-06-17', 10, 'Mon,Tue,Wed,Thu,Fri,'),
227(7, '2016-05-02', '2016-05-13', 12, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
228(8, '2016-04-30', '2016-05-14', 15, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
229(9, '2016-05-02', '2016-05-17', 8, 'Mon,Tue,Wed,'),
230(10,'2016-05-05', '2016-05-22', 12, 'Sun,Thu,Fri,Sat,'),
231(11,'2016-05-03', '2016-05-17', 3, 'Tue,'),
232(11,'2016-05-19', '2016-06-02', 3, 'Thu,'),
233(12,'2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
234(12,'2016-05-16', '2016-05-20', 5, 'Mon,Tue,Wed,Thu,Fri,');
235
236SELECT ContractID, StartDT, EndDT, DayCount, WeekDays
237FROM @Dst
238ORDER BY ContractID, StartDT;
239
240SET DATEFIRST 1 -- Make Monday weekday=1
241
242DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the right order
243 ID int NOT NULL UNIQUE, ContractID int NOT NULL, dt date, -- Original relevant values (ID is not really necessary)
244 WeekNo int NOT NULL, dowBit int NOT NULL); -- Useful to find gaps in days or weeks
245INSERT INTO @Ranked
246SELECT ID, ContractID, dt,
247 DATEDIFF(WEEK, '1900-01-01', DATEADD(DAY, 1-DATEPART(dw, dt), dt)) AS WeekNo,
248 POWER(2, DATEPART(dw, dt)-1) AS dowBit
249FROM @Src
250ORDER BY ContractID, WeekNo, dowBit
251
252/*
253Each evaluated date makes part of the carried sequence if:
254 - this is not a new contract, and
255 - sequence started this week, or
256 - same day last week was part of the sequence, or
257 - sequence started last week and today is a lower day than the accumulated weekdays list
258 - and there are no sequence gaps since previous day
259(otherwise it does not make part of the old sequence, so it starts a new one) */
260
261DECLARE @RankedRanges TABLE (RowID int NOT NULL PRIMARY KEY, WeekDays int NOT NULL, StartRowID int NULL);
262
263WITH WeeksCTE AS -- Needed for building the sequence gradually, and comparing the carried sequence (and previous day) with a current evaluated day
264(
265 SELECT RowID, ContractID, dowBit, WeekNo, RowID AS StartRowID, WeekNo AS StartWN, dowBit AS WeekDays, dowBit AS StartWeekDays
266 FROM @Ranked
267 WHERE RowID = 1
268 UNION ALL
269 SELECT RowID, ContractID, dowBit, WeekNo, StartRowID,
270 CASE WHEN StartRowID IS NULL THEN StartWN ELSE WeekNo END AS WeekNo,
271 CASE WHEN StartRowID IS NULL THEN WeekDays | dowBit ELSE dowBit END AS WeekDays,
272 CASE WHEN StartRowID IS NOT NULL THEN dowBit WHEN WeekNo = StartWN THEN StartWeekDays | dowBit ELSE StartWeekDays END AS StartWeekDays
273 FROM (
274 SELECT w.*, pre.StartWN, pre.WeekDays, pre.StartWeekDays,
275 CASE WHEN w.ContractID <> pre.ContractID OR -- New contract always break the sequence
276 NOT (w.WeekNo = pre.StartWN OR -- Same week as a new sequence always keeps the sequence
277 w.dowBit & pre.WeekDays > 0 OR -- Days in the sequence keep the sequence (provided there are no gaps, checked later)
278 (w.WeekNo = pre.StartWN+1 AND (w.dowBit-1) & pre.StartWeekDays = 0)) OR -- Days in the second week when less than a week passed since the sequence started remain in sequence
279 (w.WeekNo > pre.StartWN AND -- look for gap after initial week
280 w.WeekNo > pre.WeekNo+1 OR -- look for full-week gaps
281 (w.WeekNo = pre.WeekNo AND -- when same week as previous day,
282 ((w.dowBit-1) ^ (pre.dowBit*2-1)) & pre.WeekDays > 0 -- days between this and previous weekdays, compared to current series
283 ) OR
284 (w.WeekNo > pre.WeekNo AND -- when following week of previous day,
285 ((-1 ^ (pre.dowBit*2-1)) | (w.dowBit-1)) & pre.WeekDays > 0 -- days between this and previous weekdays, compared to current series
286 )) THEN w.RowID END AS StartRowID
287 FROM WeeksCTE pre
288 JOIN @Ranked w ON (w.RowID = pre.RowID + 1)
289 ) w
290)
291INSERT INTO @RankedRanges -- days sequence and starting point of each sequence
292SELECT RowID, WeekDays, StartRowID
293--SELECT *
294FROM WeeksCTE
295OPTION (MAXRECURSION 0)
296
297--SELECT * FROM @RankedRanges
298
299DECLARE @Ranges TABLE (RowNo int NOT NULL IDENTITY PRIMARY KEY, RowID int NOT NULL);
300
301INSERT INTO @Ranges -- @RankedRanges filtered only by start of each range, with numbered rows to easily find the end of each range
302SELECT StartRowID
303FROM @RankedRanges
304WHERE StartRowID IS NOT NULL
305ORDER BY 1
306
307-- Final result putting everything together
308SELECT rs.ContractID, rs.dt AS StartDT, re.dt AS EndDT, re.RowID-rs.RowID+1 AS DayCount,
309 CASE WHEN rr.WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
310 CASE WHEN rr.WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
311 CASE WHEN rr.WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
312 CASE WHEN rr.WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
313 CASE WHEN rr.WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
314 CASE WHEN rr.WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
315 CASE WHEN rr.WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
316FROM (
317 SELECT r.RowID AS StartRowID, COALESCE(pos.RowID-1, (SELECT MAX(RowID) FROM @Ranked)) AS EndRowID
318 FROM @Ranges r
319 LEFT JOIN @Ranges pos ON (pos.RowNo = r.RowNo + 1)
320 ) g
321JOIN @Ranked rs ON (rs.RowID = g.StartRowID)
322JOIN @Ranked re ON (re.RowID = g.EndRowID)
323JOIN @RankedRanges rr ON (rr.RowID = re.RowID)
324
325SET DATEFIRST 1 -- Make Monday weekday=1
326
327-- Get the minimum information needed to calculate sequences
328DECLARE @Days TABLE (ContractID int NOT NULL, dt date, DayNo int NOT NULL, dowBit int NOT NULL, PRIMARY KEY (ContractID, DayNo));
329INSERT INTO @Days
330SELECT ContractID, dt, CAST(CAST(dt AS datetime) AS int) AS DayNo, POWER(2, DATEPART(dw, dt)-1) AS dowBit
331FROM @Src
332
333DECLARE @RangeStartFirstPass TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo))
334
335-- Calculate, from the above list, which days are not present in the previous 7
336INSERT INTO @RangeStartFirstPass
337SELECT r.ContractID, r.DayNo
338FROM @Days r
339LEFT JOIN @Days pr ON (pr.ContractID = r.ContractID AND pr.DayNo BETWEEN r.DayNo-7 AND r.DayNo-1) -- Last 7 days
340GROUP BY r.ContractID, r.DayNo, r.dowBit
341HAVING r.dowBit & COALESCE(SUM(pr.dowBit), 0) = 0
342
343-- Update the previous list with all days that occur right after a missing day
344INSERT INTO @RangeStartFirstPass
345SELECT *
346FROM (
347 SELECT DISTINCT ContractID, (SELECT MIN(DayNo) FROM @Days WHERE ContractID = d.ContractID AND DayNo > d.DayNo + 7) AS DayNo
348 FROM @Days d
349 WHERE NOT EXISTS (SELECT 1 FROM @Days WHERE ContractID = d.ContractID AND DayNo = d.DayNo + 7)
350 ) d
351WHERE DayNo IS NOT NULL AND
352 NOT EXISTS (SELECT 1 FROM @RangeStartFirstPass WHERE ContractID = d.ContractID AND DayNo = d.DayNo)
353
354DECLARE @RangeStart TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo));
355
356-- Fetch the first sequence for each contract
357INSERT INTO @RangeStart
358SELECT ContractID, MIN(DayNo)
359FROM @RangeStartFirstPass
360GROUP BY ContractID
361
362-- Add to the list above the next sequence for each contract, until all are added
363-- (ensure no sequence is added with less than 7 days)
364WHILE @@ROWCOUNT > 0
365 INSERT INTO @RangeStart
366 SELECT f.ContractID, MIN(f.DayNo)
367 FROM (SELECT ContractID, MAX(DayNo) AS DayNo FROM @RangeStart GROUP BY ContractID) s
368 JOIN @RangeStartFirstPass f ON (f.ContractID = s.ContractID AND f.DayNo > s.DayNo + 7)
369 GROUP BY f.ContractID
370
371-- Summarise results
372SELECT ContractID, StartDT, EndDT, DayCount,
373 CASE WHEN WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
374 CASE WHEN WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
375 CASE WHEN WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
376 CASE WHEN WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
377 CASE WHEN WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
378 CASE WHEN WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
379 CASE WHEN WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
380FROM (
381 SELECT r.ContractID,
382 MIN(d.dt) AS StartDT,
383 MAX(d.dt) AS EndDT,
384 COUNT(*) AS DayCount,
385 SUM(DISTINCT d.dowBit) AS WeekDays
386 FROM (SELECT *, COALESCE((SELECT MIN(DayNo) FROM @RangeStart WHERE ContractID = rs.ContractID AND DayNo > rs.DayNo), 999999) AS DayEnd FROM @RangeStart rs) r
387 JOIN @Days d ON (d.ContractID = r.ContractID AND d.DayNo BETWEEN r.DayNo AND r.DayEnd-1)
388 GROUP BY r.ContractID, r.DayNo
389 ) d
390ORDER BY ContractID, StartDT
391
392WITH
393 mysrc AS (
394 SELECT *, RANK() OVER (PARTITION BY ContractID ORDER BY DT) AS rank
395 FROM @Src
396 ),
397 prepos AS (
398 SELECT s.*, pos.ID AS posid
399 FROM mysrc s
400 LEFT JOIN mysrc pos ON (pos.ContractID = s.ContractID AND pos.rank = s.rank+1 AND (pos.DowInt = s.DowInt+1 OR pos.DowInt = 2 AND s.DowInt=6))
401 ),
402 grped AS (
403 SELECT TOP 100 *, (SELECT COUNT(CASE WHEN posid IS NULL THEN 1 END) FROM prepos WHERE contractid = p.contractid AND rank < p.rank) as grp
404 FROM prepos p
405 ORDER BY ContractID, DT
406 )
407SELECT ContractID, min(dt) AS StartDT, max(dt) AS EndDT, count(*) AS DayCount,
408 STUFF( (SELECT ', ' + dowchar
409 FROM (
410 SELECT TOP 100 dowint, dowchar
411 FROM grped
412 WHERE ContractID = g.ContractID AND grp = g.grp
413 GROUP BY dowint, dowchar
414 ORDER BY 1
415 ) a
416 FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 2, '') AS WeekDays
417FROM grped g
418GROUP BY ContractID, grp
419ORDER BY 1, 2
420
421+------------+------------+------------+----------+-----------------------------------+
422| ContractID | StartDT | EndDT | DayCount | WeekDays |
423+------------+------------+------------+----------+-----------------------------------+
424| 1 | 2/05/2016 | 13/05/2016 | 10 | Mon, Tue, Wed, Thu, Fri |
425| 2 | 5/05/2016 | 17/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
426| 3 | 2/05/2016 | 2/05/2016 | 1 | Mon |
427| 3 | 4/05/2016 | 4/05/2016 | 1 | Wed |
428| 3 | 6/05/2016 | 9/05/2016 | 2 | Mon, Fri |
429| 3 | 11/05/2016 | 11/05/2016 | 1 | Wed |
430| 3 | 13/05/2016 | 16/05/2016 | 2 | Mon, Fri |
431| 4 | 2/05/2016 | 6/05/2016 | 5 | Mon, Tue, Wed, Thu, Fri |
432| 4 | 10/05/2016 | 13/05/2016 | 4 | Tue, Wed, Thu, Fri |
433| 5 | 2/05/2016 | 6/05/2016 | 5 | Mon, Tue, Wed, Thu, Fri |
434| 5 | 10/05/2016 | 20/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
435| 6 | 5/05/2016 | 17/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
436| 6 | 6/06/2016 | 17/06/2016 | 10 | Mon, Tue, Wed, Thu, Fri |
437| 7 | 2/05/2016 | 7/05/2016 | 6 | Mon, Tue, Wed, Thu, Fri, Sat |
438| 7 | 8/05/2016 | 13/05/2016 | 6 | Sun, Mon, Tue, Wed, Thu, Fri |
439| 8 | 30/04/2016 | 30/04/2016 | 1 | Sat |
440| 8 | 1/05/2016 | 7/05/2016 | 7 | Sun, Mon, Tue, Wed, Thu, Fri, Sat |
441| 8 | 8/05/2016 | 14/05/2016 | 7 | Sun, Mon, Tue, Wed, Thu, Fri, Sat |
442| 9 | 2/05/2016 | 4/05/2016 | 3 | Mon, Tue, Wed |
443| 9 | 9/05/2016 | 10/05/2016 | 2 | Mon, Tue |
444+------------+------------+------------+----------+-----------------------------------+
445
446WITH
447CTE_ContractDays
448AS
449(
450 SELECT
451 S.ContractID
452 ,MIN(S.dt) OVER (PARTITION BY S.ContractID) AS ContractMinDT
453 ,S.dt
454 ,ROW_NUMBER() OVER (PARTITION BY S.ContractID ORDER BY S.dt) AS rn1
455 ,DATEDIFF(day, '2001-01-01', S.dt) AS DayNumber
456 ,S.dowChar
457 ,S.dowInt
458 FROM
459 @Src AS S
460)
461,CTE_DailyRN
462AS
463(
464 SELECT
465 DayNumber - rn1 AS WeekGroupNumber
466 ,ROW_NUMBER() OVER (
467 PARTITION BY
468 ContractID
469 ,DayNumber - rn1
470 ORDER BY dt) AS rn2
471 ,ContractID
472 ,ContractMinDT
473 ,dt
474 ,rn1
475 ,DayNumber
476 ,dowChar
477 ,dowInt
478 FROM CTE_ContractDays
479)
480,CTE_DailyIslands
481AS
482(
483 SELECT
484 ContractID
485 ,ContractMinDT
486 ,MIN(dt) AS MinDT
487 ,MAX(dt) AS MaxDT
488 ,COUNT(*) AS DayCount
489 -- '2001-01-01' is Monday
490 ,DATEDIFF(day, '2001-01-01', MIN(dt)) / 7 AS WeekNumberMin
491 ,DATEDIFF(day, '2001-01-01', MAX(dt)) / 7 AS WeekNumberMax
492 FROM CTE_DailyRN
493 GROUP BY
494 ContractID
495 ,rn1-rn2
496 ,ContractMinDT
497)
498,CTE_Weeks
499AS
500(
501 SELECT
502 ContractID
503 ,ContractMinDT
504 ,MIN(MinDT) AS MinDT
505 ,MAX(MaxDT) AS MaxDT
506 ,SUM(DayCount) AS DayCount
507 ,WeekNumberMin
508 ,WeekNumberMax
509 FROM CTE_DailyIslands
510 GROUP BY
511 ContractID
512 ,ContractMinDT
513 ,WeekNumberMin
514 ,WeekNumberMax
515)
516,CTE_FirstResult
517AS
518(
519 SELECT
520 ContractID
521 ,ContractMinDT
522 ,MinDT
523 ,MaxDT
524 ,DayCount
525 ,CA_Data.XML_Value AS DaysOfWeek
526 ,WeekNumberMin AS WeekNumber
527 ,ROW_NUMBER() OVER(PARTITION BY ContractID ORDER BY MinDT) AS rn1
528 FROM
529 CTE_Weeks
530 CROSS APPLY
531 (
532 SELECT CAST(CTE_ContractDays.dowChar AS varchar(8000)) + ',' AS dw
533 FROM CTE_ContractDays
534 WHERE
535 CTE_ContractDays.ContractID = CTE_Weeks.ContractID
536 AND CTE_ContractDays.dt >= CTE_Weeks.MinDT
537 AND CTE_ContractDays.dt <= CTE_Weeks.MaxDT
538 GROUP BY
539 CTE_ContractDays.dowChar
540 ,CTE_ContractDays.dowInt
541 ORDER BY CTE_ContractDays.dowInt
542 FOR XML PATH(''), TYPE
543 ) AS CA_XML(XML_Value)
544 CROSS APPLY
545 (
546 SELECT CA_XML.XML_Value.value('.', 'VARCHAR(8000)')
547 ) AS CA_Data(XML_Value)
548)
549,CTE_SecondRN
550AS
551(
552 SELECT
553 ContractID
554 ,ContractMinDT
555 ,MinDT
556 ,MaxDT
557 ,DayCount
558 ,DaysOfWeek
559 ,WeekNumber
560 ,rn1
561 ,WeekNumber - rn1 AS SecondGroupNumber
562 ,ROW_NUMBER() OVER (
563 PARTITION BY
564 ContractID
565 ,DaysOfWeek
566 ,DayCount
567 ,WeekNumber - rn1
568 ORDER BY MinDT) AS rn2
569 FROM CTE_FirstResult
570)
571,CTE_Schedules
572AS
573(
574 SELECT
575 ContractID
576 ,MIN(MinDT) AS StartDT
577 ,MAX(MaxDT) AS EndDT
578 ,SUM(DayCount) AS DayCount
579 ,DaysOfWeek
580 FROM CTE_SecondRN
581 GROUP BY
582 ContractID
583 ,DaysOfWeek
584 ,rn1-rn2
585)
586SELECT
587 ContractID
588 ,StartDT
589 ,EndDT
590 ,DayCount
591 ,DaysOfWeek AS WeekDays
592FROM CTE_Schedules
593ORDER BY
594 ContractID
595 ,StartDT
596;
597
598+------------+------------+------------+----------+------------------------------+
599| ContractID | StartDT | EndDT | DayCount | WeekDays |
600+------------+------------+------------+----------+------------------------------+
601| 1 | 2016-05-02 | 2016-05-13 | 10 | Mon,Tue,Wed,Thu,Fri, |
602| 2 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
603| 2 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
604| 2 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
605| 3 | 2016-05-02 | 2016-05-13 | 6 | Mon,Wed,Fri, |
606| 3 | 2016-05-16 | 2016-05-16 | 1 | Mon, |
607| 4 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
608| 4 | 2016-05-10 | 2016-05-13 | 4 | Tue,Wed,Thu,Fri, |
609| 5 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
610| 5 | 2016-05-10 | 2016-05-13 | 4 | Tue,Wed,Thu,Fri, |
611| 5 | 2016-05-16 | 2016-05-20 | 5 | Mon,Tue,Wed,Thu,Fri, |
612| 6 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
613| 6 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
614| 6 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
615| 6 | 2016-06-06 | 2016-06-17 | 10 | Mon,Tue,Wed,Thu,Fri, |
616| 7 | 2016-05-02 | 2016-05-13 | 12 | Sun,Mon,Tue,Wed,Thu,Fri,Sat, |
617| 8 | 2016-04-30 | 2016-05-14 | 15 | Sun,Mon,Tue,Wed,Thu,Fri,Sat, |
618| 9 | 2016-05-02 | 2016-05-11 | 6 | Mon,Tue,Wed, |
619| 9 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
620| 10 | 2016-05-05 | 2016-05-22 | 12 | Sun,Thu,Fri,Sat, |
621| 11 | 2016-05-03 | 2016-05-10 | 2 | Tue, |
622| 11 | 2016-05-17 | 2016-05-19 | 2 | Tue,Thu, |
623| 11 | 2016-05-26 | 2016-06-02 | 2 | Thu, |
624| 12 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
625| 12 | 2016-05-16 | 2016-05-20 | 5 | Mon,Tue,Wed,Thu,Fri, |
626+------------+------------+------------+----------+------------------------------+
627
628CREATE TABLE #Dst_V2 (ContractID bigint, StartDT date, EndDT date, DayCount int, WeekDays varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS);
629
630SET NOCOUNT ON;
631
632DECLARE @VarOldDateFirst int = @@DATEFIRST;
633SET DATEFIRST 7;
634
635DECLARE @iFS int;
636DECLARE @VarCursor CURSOR;
637SET @VarCursor = CURSOR FAST_FORWARD
638FOR
639 SELECT
640 ContractID
641 ,dt
642 ,dowChar
643 ,dowInt
644 FROM #Src AS S
645 ;
646
647OPEN @VarCursor;
648
649DECLARE @CurrContractID bigint = 0;
650DECLARE @Currdt date;
651DECLARE @CurrdowChar char(3);
652DECLARE @CurrdowInt int;
653
654
655DECLARE @VarCreateNewInterval bit = 0;
656DECLARE @VarTempDT date;
657DECLARE @VarTempdowInt int;
658
659DECLARE @LastContractID bigint = 0;
660DECLARE @LastStartDT date;
661DECLARE @LastEndDT date;
662DECLARE @LastDayCount int = 0;
663DECLARE @LastWeekDays varchar(255);
664DECLARE @LastMonCount int;
665DECLARE @LastTueCount int;
666DECLARE @LastWedCount int;
667DECLARE @LastThuCount int;
668DECLARE @LastFriCount int;
669DECLARE @LastSatCount int;
670DECLARE @LastSunCount int;
671
672
673FETCH NEXT FROM @VarCursor INTO @CurrContractID, @Currdt, @CurrdowChar, @CurrdowInt;
674SET @iFS = @@FETCH_STATUS;
675IF @iFS = 0
676BEGIN
677 SET @LastContractID = @CurrContractID;
678 SET @LastStartDT = @Currdt;
679 SET @LastEndDT = @Currdt;
680 SET @LastDayCount = 1;
681 SET @LastMonCount = 0;
682 SET @LastTueCount = 0;
683 SET @LastWedCount = 0;
684 SET @LastThuCount = 0;
685 SET @LastFriCount = 0;
686 SET @LastSatCount = 0;
687 SET @LastSunCount = 0;
688 IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
689 IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
690 IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
691 IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
692 IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
693 IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
694 IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
695END;
696
697WHILE @iFS = 0
698BEGIN
699
700 SET @VarCreateNewInterval = 0;
701
702 -- Contract changes -> start new interval
703 IF @LastContractID <> @CurrContractID
704 BEGIN
705 SET @VarCreateNewInterval = 1;
706 END;
707
708 IF @VarCreateNewInterval = 0
709 BEGIN
710 -- check days of week
711 -- are we still within the first week of the interval?
712 IF DATEDIFF(day, @LastStartDT, @Currdt) > 6
713 BEGIN
714 -- we are beyond the first week, check day of the week
715 -- have we seen @CurrdowInt before?
716 -- we should start a new interval if this is the new day of the week that didn't exist in the first week
717 IF @CurrdowInt = 1 AND @LastSunCount = 0 SET @VarCreateNewInterval = 1;
718 IF @CurrdowInt = 2 AND @LastMonCount = 0 SET @VarCreateNewInterval = 1;
719 IF @CurrdowInt = 3 AND @LastTueCount = 0 SET @VarCreateNewInterval = 1;
720 IF @CurrdowInt = 4 AND @LastWedCount = 0 SET @VarCreateNewInterval = 1;
721 IF @CurrdowInt = 5 AND @LastThuCount = 0 SET @VarCreateNewInterval = 1;
722 IF @CurrdowInt = 6 AND @LastFriCount = 0 SET @VarCreateNewInterval = 1;
723 IF @CurrdowInt = 7 AND @LastSatCount = 0 SET @VarCreateNewInterval = 1;
724
725 IF @VarCreateNewInterval = 0
726 BEGIN
727 -- check the gap between current day and last day of the interval
728 -- if the gap between current day and last day of the interval
729 -- contains a day of the week that was included in the interval before,
730 -- we should create new interval
731 SET @VarTempDT = DATEADD(day, 1, @LastEndDT);
732 WHILE @VarTempDT < @Currdt
733 BEGIN
734 SET @VarTempdowInt = DATEPART(WEEKDAY, @VarTempDT);
735
736 IF @VarTempdowInt = 1 AND @LastSunCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
737 IF @VarTempdowInt = 2 AND @LastMonCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
738 IF @VarTempdowInt = 3 AND @LastTueCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
739 IF @VarTempdowInt = 4 AND @LastWedCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
740 IF @VarTempdowInt = 5 AND @LastThuCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
741 IF @VarTempdowInt = 6 AND @LastFriCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
742 IF @VarTempdowInt = 7 AND @LastSatCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
743
744 SET @VarTempDT = DATEADD(day, 1, @VarTempDT);
745 END;
746 END;
747 END;
748 -- else
749 -- we are still within the first week, so we can add this day to the interval
750 END;
751
752 IF @VarCreateNewInterval = 1
753 BEGIN
754 -- save the new interval into the final table
755 SET @LastWeekDays = '';
756 IF @LastSunCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sun,';
757 IF @LastMonCount > 0 SET @LastWeekDays = @LastWeekDays + 'Mon,';
758 IF @LastTueCount > 0 SET @LastWeekDays = @LastWeekDays + 'Tue,';
759 IF @LastWedCount > 0 SET @LastWeekDays = @LastWeekDays + 'Wed,';
760 IF @LastThuCount > 0 SET @LastWeekDays = @LastWeekDays + 'Thu,';
761 IF @LastFriCount > 0 SET @LastWeekDays = @LastWeekDays + 'Fri,';
762 IF @LastSatCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sat,';
763
764 INSERT INTO #Dst_V2
765 (ContractID
766 ,StartDT
767 ,EndDT
768 ,DayCount
769 ,WeekDays)
770 VALUES
771 (@LastContractID
772 ,@LastStartDT
773 ,@LastEndDT
774 ,@LastDayCount
775 ,@LastWeekDays);
776
777 -- init the new interval
778 SET @LastContractID = @CurrContractID;
779 SET @LastStartDT = @Currdt;
780 SET @LastEndDT = @Currdt;
781 SET @LastDayCount = 1;
782 SET @LastMonCount = 0;
783 SET @LastTueCount = 0;
784 SET @LastWedCount = 0;
785 SET @LastThuCount = 0;
786 SET @LastFriCount = 0;
787 SET @LastSatCount = 0;
788 SET @LastSunCount = 0;
789 IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
790 IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
791 IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
792 IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
793 IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
794 IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
795 IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
796
797 END ELSE BEGIN
798
799 -- update last interval
800 SET @LastEndDT = @Currdt;
801 SET @LastDayCount = @LastDayCount + 1;
802 IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
803 IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
804 IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
805 IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
806 IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
807 IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
808 IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
809 END;
810
811
812 FETCH NEXT FROM @VarCursor INTO @CurrContractID, @Currdt, @CurrdowChar, @CurrdowInt;
813 SET @iFS = @@FETCH_STATUS;
814END;
815
816-- save the last interval into the final table
817IF @LastDayCount > 0
818BEGIN
819 SET @LastWeekDays = '';
820 IF @LastSunCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sun,';
821 IF @LastMonCount > 0 SET @LastWeekDays = @LastWeekDays + 'Mon,';
822 IF @LastTueCount > 0 SET @LastWeekDays = @LastWeekDays + 'Tue,';
823 IF @LastWedCount > 0 SET @LastWeekDays = @LastWeekDays + 'Wed,';
824 IF @LastThuCount > 0 SET @LastWeekDays = @LastWeekDays + 'Thu,';
825 IF @LastFriCount > 0 SET @LastWeekDays = @LastWeekDays + 'Fri,';
826 IF @LastSatCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sat,';
827
828 INSERT INTO #Dst_V2
829 (ContractID
830 ,StartDT
831 ,EndDT
832 ,DayCount
833 ,WeekDays)
834 VALUES
835 (@LastContractID
836 ,@LastStartDT
837 ,@LastEndDT
838 ,@LastDayCount
839 ,@LastWeekDays);
840END;
841
842CLOSE @VarCursor;
843DEALLOCATE @VarCursor;
844
845SET DATEFIRST @VarOldDateFirst;
846
847DROP TABLE #Dst_V2;
848
849IF OBJECT_ID('tempdb..#srcWithRn') IS NOT NULL
850 DROP TABLE #srcWithRn
851GO
852SELECT rn = IDENTITY(INT, 1, 1), ContractId, dt, dowInt,
853 POWER(2, dowInt) AS dowPower, dowChar
854INTO #srcWithRn
855FROM #src
856ORDER BY ContractId, dt
857GO
858ALTER TABLE #srcWithRn
859ADD PRIMARY KEY (rn)
860GO
861
862DECLARE @ContractId INT, @RnList VARCHAR(MAX), @NewGrouping BIT = 0, @DowBitmap INT = 0, @startDt DATE
863SELECT TOP 1 @ContractId = ContractId, @startDt = dt, @RnList = ',' + CONVERT(VARCHAR(MAX), rn), @DowBitmap = DowPower
864FROM #srcWithRn
865WHERE rn = 1
866
867SELECT
868 -- New grouping if new contract, or if we're observing a new day that we did
869 -- not observe within the first 7 days of the grouping
870 @NewGrouping = CASE
871 WHEN ContractId <> @ContractId THEN 1
872 WHEN DATEDIFF(DAY, @startDt, dt) > 6
873 AND @DowBitmap & dowPower <> dowPower THEN 1
874 ELSE 0
875 END,
876 @ContractId = ContractId,
877 -- If this is a newly observed day in an existing grouping, add it to the bitmap
878 @DowBitmap = CASE WHEN @NewGrouping = 0 THEN @DowBitmap | DowPower ELSE DowPower END,
879 -- If this is a new grouping, reset the start date of the grouping
880 @startDt = CASE WHEN @NewGrouping = 0 THEN @startDt ELSE dt END,
881 -- If this is a new grouping, add this rn to the list of row numbers that delineate the boundary of a new grouping
882 @RnList = CASE WHEN @NewGrouping = 0 THEN @RnList ELSE @RnList + ',' + CONVERT(VARCHAR(MAX), rn) END
883FROM #srcWithRn
884WHERE rn >= 2
885ORDER BY rn
886OPTION (MAXDOP 1)
887
888-- Split the list of grouping boundaries into a table
889IF OBJECT_ID('tempdb..#newGroupingRns') IS NOT NULL
890 DROP TABLE #newGroupingRns
891SELECT splitListId AS rn
892INTO #newGroupingRns
893FROM dbo.f_delimitedIntListSplitter(SUBSTRING(@RnList, 2, 1000000000), DEFAULT)
894GO
895ALTER TABLE #newGroupingRns
896ADD PRIMARY KEY (rn)
897GO
898
899IF OBJECT_ID('tempdb..#finalGroupings') IS NOT NULL
900 DROP TABLE #finalGroupings
901GO
902SELECT MIN(s.ContractId) AS ContractId,
903 MIN(dt) AS StartDT,
904 MAX(dt) AS EndDT,
905 COUNT(*) AS DayCount,
906 CASE WHEN MAX(CASE WHEN dowChar = 'Sun' THEN 1 ELSE 0 END) = 1 THEN 'Sun,' ELSE '' END +
907 CASE WHEN MAX(CASE WHEN dowChar = 'Mon' THEN 1 ELSE 0 END) = 1 THEN 'Mon,' ELSE '' END +
908 CASE WHEN MAX(CASE WHEN dowChar = 'Tue' THEN 1 ELSE 0 END) = 1 THEN 'Tue,' ELSE '' END +
909 CASE WHEN MAX(CASE WHEN dowChar = 'Wed' THEN 1 ELSE 0 END) = 1 THEN 'Wed,' ELSE '' END +
910 CASE WHEN MAX(CASE WHEN dowChar = 'Thu' THEN 1 ELSE 0 END) = 1 THEN 'Thu,' ELSE '' END +
911 CASE WHEN MAX(CASE WHEN dowChar = 'Fri' THEN 1 ELSE 0 END) = 1 THEN 'Fri,' ELSE '' END +
912 CASE WHEN MAX(CASE WHEN dowChar = 'Sat' THEN 1 ELSE 0 END) = 1 THEN 'Sat,' ELSE '' END AS WeekDays
913INTO #finalGroupings
914FROM #srcWithRn s
915CROSS APPLY (
916 -- For any row, its grouping is the largest boundary row number that occurs at or before this row
917 SELECT TOP 1 rn AS groupingRn
918 FROM #newGroupingRns grp
919 WHERE grp.rn <= s.rn
920 ORDER BY grp.rn DESC
921) g
922GROUP BY g.groupingRn
923ORDER BY g.groupingRn
924GO
925
926declare @Helper table(
927 rn tinyint,
928 dowInt tinyint,
929 dowChar char(3));
930insert @Helper
931values ( 1,1,'Sun'),
932 ( 2,2,'Mon'),
933 ( 3,3,'Tue'),
934 ( 4,4,'Wed'),
935 ( 5,5,'Thu'),
936 ( 6,6,'Fri'),
937 ( 7,7,'Sat'),
938 ( 8,1,'Sun'),
939 ( 9,2,'Mon'),
940 (10,3,'Tue'),
941 (11,4,'Wed'),
942 (12,5,'Thu'),
943 (13,6,'Fri'),
944 (14,7,'Sat');
945
946
947
948with MissingDays as
949(
950 select
951 h1.rn as rn1,
952 h1.dowChar as StartDay,
953 h2.rn as rn2,
954 h2.dowInt as FollowingDayInt,
955 h2.dowChar as FollowingDayChar
956 from @Helper as h1
957 inner join @Helper as h2
958 on h2.rn > h1.rn
959 where h1.rn < 8
960 and h2.rn < h1.rn + 8
961)
962,Numbered as
963(
964 select
965 a.*,
966 ROW_NUMBER() over (partition by a.ContractID order by a.dt) as rn
967 from #Src as a
968)
969,Incremented as
970(
971 select
972 b.*,
973 convert(varchar(max), b.dowChar)+',' as WeekDays,
974 b.dt as IntervalStart
975 from Numbered as b
976 where b.rn = 1
977
978 union all
979
980 select
981 c.*,
982 case
983 when
984 (DATEDIFF(day, d.IntervalStart, c.dt) > 6) -- interval goes beyond 7 days
985 and (
986 (d.WeekDays not like '%'+c.dowChar+'%') -- the new week day has not been seen before
987 or
988 (DATEDIFF(day, d.dt, c.dt) > 7)
989 or
990 (
991 (DATEDIFF(day, d.dt, c.dt) > 1)
992 and
993 (
994 exists( select
995 e.FollowingDayChar
996 from MissingDays as e
997 where e.StartDay = d.dowChar
998 and rn2 < (select f.rn2 from MissingDays as f
999 where f.StartDay = d.dowChar
1000 and f.FollowingDayInt = c.dowInt)
1001 and d.WeekDays like '%'+e.FollowingDayChar+'%'
1002 )
1003 )
1004 )
1005 )
1006 then convert(varchar(max),c.dowChar)+','
1007 else
1008 case
1009 when d.WeekDays like '%'+c.dowChar+'%'
1010 then d.WeekDays
1011 else d.WeekDays+convert(varchar(max),c.dowChar)+','
1012 end
1013 end,
1014 case
1015 when
1016 (DATEDIFF(day, d.IntervalStart, c.dt) > 6) -- interval goes beyond 7 days
1017 and (
1018 (d.WeekDays not like '%'+c.dowChar+'%') -- the new week day has not been seen before
1019 or
1020 (DATEDIFF(day, d.dt, c.dt) > 7) -- there is a one week gap
1021 or
1022 (
1023 (DATEDIFF(day, d.dt, c.dt) > 1) -- there is a gap..
1024 and
1025 (
1026 exists( select -- .. and the omitted days are in the preceeding interval
1027 e.FollowingDayChar
1028 from MissingDays as e
1029 where e.StartDay = d.dowChar
1030 and rn2 < (select f.rn2 from MissingDays as f
1031 where f.StartDay = d.dowChar
1032 and f.FollowingDayInt = c.dowInt)
1033 and d.WeekDays like '%'+e.FollowingDayChar+'%'
1034 )
1035 )
1036 )
1037 )
1038 then c.dt
1039 else d.IntervalStart
1040 end
1041 from Numbered as c
1042 inner join Incremented as d
1043 on d.ContractID = c.ContractID
1044 and d.rn = c.rn - 1
1045)
1046select
1047 g.ContractID,
1048 g.IntervalStart as StartDT,
1049 MAX(g.dt) as EndDT,
1050 COUNT(*) as DayCount,
1051 MAX(g.WeekDays) as WeekDays
1052from Incremented as g
1053group by
1054 g.ContractID,
1055 g.IntervalStart
1056order by
1057 ContractID,
1058 StartDT;
1059
1060estimated cost
1061
1062My submission as is w/ CTEs, Geoff's data: 791682
1063Geoff's data, cluster key on (ContractID, dt): 21156.2
1064Real table for MissingDays: 21156.2
1065Numbered as table UCI=(ContractID, rn): 16.6115 26s elapsed.
1066 UCI=(rn, ContractID): 41.9845 26s elapsed.
1067MissingDays as refactored to simple lookup 16.6477 22s elapsed.
1068Weekdays as varchar(30) 13.4013 30s elapsed.
1069
1070Table 'Worktable'. Scan count 2, logical reads 4 196 269, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1071Table 'MissingDays'. Scan count 464 116, logical reads 928 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1072Table 'Numbered'. Scan count 484 122, logical reads 1 475 467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.