将长时间的多日时间段分解为几个单日时间段

本文介绍了将长时间的多日时间段分解为几个单日时间段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一些数据存储在这样的表中:

I have some data stored in a table like this:

VacationId VacationDate VacationDuration_Hr 1 2018/01/24 4 2 2018/03/21 60 3 2018/08/16 33 4 2018/12/01 8

我希望能够将较长的时间段分解为几个较短的时间段，最大长度为 24 小时，如下所示:

I'd like to be able to break down the longer time periods into several shorter ones with the max length of 24hr like this:

VacationDate VacationDuration_Hr 2018/01/24 4 2018/03/21 24 2018/03/22 24 2018/03/23 12 2018/08/16 24 2018/08/17 9 2018/12/01 8

没有游标有什么技巧吗?提前致谢

Is there a trick to do it without cursors? Thanks In advance

推荐答案

我在这里使用的方法是创建额外行的 Tally Table.我 JOIN 到计数表上，其中小时数/24(整数数学很有用)大于计数值，然后可以使用它来计算小时数.

The method I have used here is a Tally Table to create extra rows. I JOIN onto the tally table where the number of hours / 24 (integer maths is useful) is greater than the tally number, and then can use that to calculate the hours.

WITH YourTable AS( SELECT * FROM (VALUES(1,CONVERT(date,'2018/01/24',111),4 ), (2,CONVERT(date,'2018/03/21',111),40), (3,CONVERT(date,'2018/08/16',111),33), (4,CONVERT(date,'2018/12/01',111),8 ), (5,CONVERT(date,'2018/12/17',111),56 ), (6,CONVERT(date,'2018/12/17',111),24 ))V(VacationID,VacationDate,VacationDuration_Hr)), --Solution N AS( SELECT N FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL))N(N)), Tally AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I FROM N N1, N N2) -- 25 days, add more for more days SELECT YT.VacationID, DATEADD(DAY, T.I, YT.VacationDate) AS VacationDate, CASE WHEN VacationDuration_Hr - (T.I * 24) > 24 THEN 24 ELSE YT.VacationDuration_Hr - (T.I * 24) END AS VacationDuration_Hr FROM YourTable YT JOIN Tally T ON (YT.VacationDuration_Hr -1) / 24 >= T.I ORDER BY YT.VacationID, T.I;

您可以也可以在这里使用 rCTE，但是，我倾向于避免使用 rCTE；特别是当我不知道 VacationDuration_Hr 的值的上限可能是什么时.如果它很大，则可能会对性能产生一些不利影响，并且 Tally 的性能将明显优于 rCTE 的 RBAR 性质.

You could also use an rCTE here instead, however, I tend to avoid those for things like this; especially when I have no context of what the upper limit to the value of VacationDuration_Hr could be. if it is large it could have some nasty performance implications and a Tally will significantly out perform the RBAR nature of an rCTE.