sql-server – 将每日时间表分组为[开始日期;结束日期]与工作日

样本数据和预期结果的脚本

-- @Src is sample data
-- @Dst is expected result

DECLARE @Src TABLE (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,'2016-05-03','Tue',3),(112,'2016-05-04','Wed',4),(113,'2016-05-05','Thu',5),(114,'2016-05-06','Fri',6),(115,'2016-05-09',(116,'2016-05-10',(117,'2016-05-11',(118,'2016-05-12',(119,'2016-05-13',-- a partial end of the week,the whole week,partial start of the week (without weekends)
(223,2,(224,(225,(226,(227,(228,(229,(230,'2016-05-16',(231,'2016-05-17',-- only Mon,Fri are included across two weeks plus partial third week
(310,3,(311,(314,(315,(317,(319,(330,-- a whole week (without weekend),in the second week Mon is not included
(410,4,(411,(412,(413,(414,(416,(417,(418,(419,-- three weeks,but without Mon in the second week (no weekends)
(510,5,(511,(512,(513,(514,(516,(517,(518,(519,(520,(521,(522,'2016-05-18',(523,'2016-05-19',(524,'2016-05-20',-- long gap between two intervals
(623,6,(624,(625,(626,(627,(628,(629,(630,(631,(645,'2016-06-06',(646,'2016-06-07',(647,'2016-06-08',(648,'2016-06-09',(649,'2016-06-10',(655,'2016-06-13',(656,'2016-06-14',(657,'2016-06-15',(658,'2016-06-16',(659,'2016-06-17',-- two weeks,no gaps between days at all,even weekends are included
(710,7,(711,(712,(713,(714,(715,'2016-05-07','Sat',7),(716,'2016-05-08','Sun',1),(725,(726,(727,(728,(729,-- no gaps between days at all,even weekends are included,with partial weeks
(805,8,'2016-04-30',(806,'2016-05-01',(810,(811,(812,(813,(814,(815,(816,(825,(826,(827,(828,(829,(830,'2016-05-14',-- only Mon-Wed included,two weeks plus partial third week
(910,9,(911,(912,(915,(916,(917,(930,(931,-- only Thu-Sun included,three weeks
(1013,10,(1014,(1015,(1016,(1018,(1019,(1020,(1021,'2016-05-15',(1023,(1024,(1025,'2016-05-21',(1026,'2016-05-22',-- only Tue for first three weeks,then only Thu for the next three weeks
(1111,11,(1116,(1131,(1123,(1124,'2016-05-26',(1125,'2016-06-02',-- one week,then one week gap,then one week
(1210,12,(1211,(1212,(1213,(1214,(1215,(1216,(1217,(1218,(1219,6);

SELECT ID,dowInt
FROM @Src
ORDER BY ContractID,dt;


DECLARE @Dst TABLE (ContractID int,StartDT date,EndDT date,DayCount int,WeekDays varchar(255));
INSERT INTO @Dst (ContractID,StartDT,EndDT,DayCount,WeekDays) VALUES
(1,'Mon,'),(2,(3,(4,'Tue,(5,(6,(7,'Sun,Mon,Sat,(8,15,(9,(10,(11,'Thu,(12,');

SELECT ContractID,WeekDays
FROM @Dst
ORDER BY ContractID,StartDT;

答案比较

（编辑：网站开发网_安阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!