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

我需要在两个系统之间转换数据.

第一个系统将日程表存储为日期的简单列表.计划中包含的每个日期都是一行.
日期顺序(周末,公众假期和更长的停顿)可能存在各种差距,一周中的某些日子可能会被排除在日程表之外.根本没有差距,甚至可以包括周末.时间表最长可达2年.通常是几周.

以下是一个简单的计划示例,该计划跨越两周,不包括周末(下面的脚本中有更复杂的示例)：

+----+------------+------------+---------+--------+
| ID | ContractID |     dt     | dowChar | dowInt |
+----+------------+------------+---------+--------+
| 10 |          1 | 2016-05-02 | Mon     |      2 |
| 11 |          1 | 2016-05-03 | Tue     |      3 |
| 12 |          1 | 2016-05-04 | Wed     |      4 |
| 13 |          1 | 2016-05-05 | Thu     |      5 |
| 14 |          1 | 2016-05-06 | Fri     |      6 |
| 15 |          1 | 2016-05-09 | Mon     |      2 |
| 16 |          1 | 2016-05-10 | Tue     |      3 |
| 17 |          1 | 2016-05-11 | Wed     |      4 |
| 18 |          1 | 2016-05-12 | Thu     |      5 |
| 19 |          1 | 2016-05-13 | Fri     |      6 |
+----+------------+------------+---------+--------+

ID是唯一的,但它不一定是顺序的(它是主键).
日期在每个合约中都是唯一的(在(ContractID,dt)上有唯一索引).

第二个系统将时间表存储为时间间隔,其中包含作为时间表一部分的工作日列表.每个间隔由其开始和结束日期(包括)和计划中包含的工作日列表定义.在这种格式中,您可以有效地定义重复的每周模式,例如周一至周三,但是当模式被中断时(例如公共假期),它会变得很痛苦.

以下简单示例如下所示：

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          1 | 2016-05-02 | 2016-05-13 |       10 | Mon,Tue,Wed,Thu,Fri,|
+------------+------------+------------+----------+----------------------+

属于同一合同的[StartDT; EndDT]间隔不应重叠.

我需要将第一个系统的数据转换为第二个系统使用的格式.
目前我在C#的客户端为单个给定的合同解决了这个问题,
但我想在服务器端的T-SQL中进行批量处理和服务器之间的导出/导入.
最有可能的是,它可以使用CLR UDF完成,但在此阶段我不能使用SQLCLR.

这里的挑战是使间隔列表尽可能简短和人性化.

例如,这个时间表：

+-----+------------+------------+---------+--------+
| ID  | ContractID |     dt     | dowChar | dowInt |
+-----+------------+------------+---------+--------+
| 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 |
+-----+------------+------------+---------+--------+

应该成为这样的：

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          2 | 2016-05-05 | 2016-05-17 |        9 | Mon,|
+------------+------------+------------+----------+----------------------+

,不是这个：

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          2 | 2016-05-05 | 2016-05-06 |        2 | Thu,|
|          2 | 2016-05-09 | 2016-05-13 |        5 | Mon,|
|          2 | 2016-05-16 | 2016-05-17 |        2 | Mon,|
+------------+------------+------------+----------+----------------------+

我尝试对这个问题应用间隙和岛屿方法.我尝试过两次通过.在第一次通过中,我发现连续几天的岛屿,即岛屿的末端是天数,天周,公众假期或其他东西的任何差距.对于每个这样的岛屿,我建立了一个以逗号分隔的不同周末列表.在第二次通过中,I组通过查看周数序列中的间隙或WeekDays的变化进一步发现了岛屿.

使用这种方法,每个部分周最终作为额外的间隔,如上所示,因为即使周数是连续的,周日也会改变.此外,一周内可能存在规律的差距(参见样本数据中的ContractID = 3,其数据仅为周一,周三,周五),这种方法会在此类计划中为每一天生成单独的间隔.好的一面是,如果时间表根本没有任何间隙,它会生成一个时间间隔(请参阅包含周末的样本数据中的ContractID = 7),在这种情况下,开始或结束周是否是部分无关紧要.

请参阅下面脚本中的其他示例,以便更好地了解我的目标.您可以看到周末被排除在外,但也可以排除一周中的任何其他日子.在示例3中,只有周一,周三和周五是计划的一部分.此外,可以包括周末,如示例7所示.解决方案应该平等地处理一周中的所有日子.可以在计划中包括或排除一周中的任何一天.

要验证生成的间隔列表是否正确描述了给定的调度,您可以使用以下伪代码：

>循环所有间隔
>对于开始日期和结束日期(包括)之间的所有日历日期的每个间隔循环.
>对于每个日期检查,是否在WeekDays中列出其星期几.如果是,则该日期包含在日程表中.

希望这可以澄清在什么情况下应该创建新的间隔.在示例4和5中,从周期的中间移除一个星期一(2016-05-09),并且这样的调度不能由单个间隔表示.在示例6中,计划中存在长的间隙,因此需要两个间隔.

间隔表示计划中的每周模式,并且当模式被中断/更改时,必须添加新的间隔.在示例11中,前三周有一个模式Tue,然后这个模式变为星期四.因此,我们需要两个时间间隔来描述这样的时间表.

我目前正在使用SQL Server 2008,因此解决方案应该适用于此版本.
如果可以使用更高版本的功能简化/改进SQL Server 2008的解决方案,这是一个奖励,请同时显示它.

（编辑：威海站长网）

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