Break down long multi day periods of time into several single day periods

Question

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

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

score 1 · Accepted Answer · answered Aug 21 '19 at 22:15

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;

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.

Thanks @Larnu I have started to evaluate the responses, can you please have a look at the revised question? Im looking for answers that can break 3day plus periods of time. Thanks — AleX_, Aug 28 '19 at 21:41
Changing the goal posts without a description doesn't help at all @AleX_ . Rather than changing the question you should be seeing if the answers you have can be used to meet your *new* goal first; by trying to change them yourself. All you've done here is invalidated both existing answers. — Thom A, Aug 28 '19 at 21:45
Would be great to hear the reason for the downvote here; if you feel this answer can be improved, please do explain why. — Thom A, Aug 29 '19 at 12:30
Thanks @AleX_ ; I'm just interested to find out why this was downvoted as if I'm not told how/why this can be improved, how can I hope to improve it? It surprises me even more that it was, when Gordon's uses an RBAR method and wasn't (something that often frowned upon). Unfortunately Gordon hasn't replied to me when I've asked him why he prefers the less performant method. — Thom A, Aug 29 '19 at 16:02
Actually I really like this way as it covers vacation durations longer than 2 days. — AleX_, Aug 29 '19 at 16:16

score 1 · Answer 2 · answered Aug 21 '19 at 22:37

1

You can use a recursive CTE for this:

with cte as (
     select VacationID, VacationDate,
            (case when VacationDuration_Hr < 24 then VacationDuration_Hr else 24 end) as day_dur,
            (case when VacationDuration_Hr < 24 then 0 else VacationDuration_Hr - 24 end) as rest
     from t
     union all
     select VacationID, dateadd(day, 1, VacationDate),
            (case when rest < 24 then rest else 24 end) as day_dur,
            (case when rest < 24 then 0 else rest - 24 end) as rest
     from cte
     where rest > 0
    )
select *
from cte;

If your vacations exceed 2400 hours, then add option (maxrecursion 0).

Here is a db<>fiddle.

answered Aug 21 '19 at 22:37

Gordon Linoff

1,242,037
58
646
786

It always surprises me that you choose to use an rCTE, Gordon, even though they're performance is significantly worse than a Tally. Is there any reason? – Thom A Aug 22 '19 at 09:21
Thanks, Gordon, I have started to evaluate the responses, can you please have a look at the revised question? I'm looking for answers that can break 3day plus periods of time. Thanks – AleX_ Aug 28 '19 at 21:42

Break down long multi day periods of time into several single day periods

2 Answers2

Linked