問題描述

我有一些數(shù)據(jù)存儲在這樣的表中:

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

我希望能夠?qū)⑤^長的時間段分解為幾個較短的時間段，最大長度為 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

沒有游標(biāo)有什么技巧嗎?提前致謝

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

推薦答案

我在這里使用的方法是創(chuàng)建額外行的 Tally Table.我 JOIN 到計數(shù)表上，其中小時數(shù)/24(整數(shù)數(shù)學(xué)很有用)大于計數(shù)值，然后可以使用它來計算小時數(shù).

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；特別是當(dāng)我不知道 VacationDuration_Hr 的值的上限可能是什么時.如果它很大，則可能會對性能產(chǎn)生一些不利影響，并且 Tally 的性能將明顯優(yōu)于 rCTE 的 RBAR 性質(zhì).

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.

這篇關(guān)于將長時間的多日時間段分解為幾個單日時間段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！