Calculate how many minutes used in a given hour

Question

Given a start and end time, I want to know how many minutes in a given hour are covered.

create function CalcMinsInHour(@start datetime, @end datetime, @hour int)
returns int
as
begin
    --Looking for best way to write this part
end

CalcMinsInHour('2012-01-18 8:15', '2012-01-18 10:30', 7)  should return 0
CalcMinsInHour('2012-01-18 8:15', '2012-01-18 10:30', 8)  should return 45
CalcMinsInHour('2012-01-18 8:15', '2012-01-18 10:30', 9)  should return 60
CalcMinsInHour('2012-01-18 8:15', '2012-01-18 10:30', 10) should return 30
CalcMinsInHour('2012-01-18 8:15', '2012-01-18 10:30', 11) should return 0

Edit: @Start and @End represent employee clock in/out times. So yes they can span two days when they work past midnight, but not more than that.

Are the dates ranges guaranteed to only be within the same day? — RQDQ, Jan 18 '12 at 19:53
How do you want to handle multiple day spans? If you enter `2012-01-18 8:15` and `2012-01-20-13:30` what would you want to get? — JNK, Jan 18 '12 at 19:53
@JNK That scenario isn't possible, but as noted in my edit something like '2012-01-18 20:15' and '2012-01-19 2:30' could be possible. I'm only looking for minutes in one given hour though that can only belong a single day. — Brandon Moore, Jan 18 '12 at 20:03

AdaTheDev · Accepted Answer · 2012-01-18T20:45:09.370

3

This should do the trick:

ALTER FUNCTION dbo.CalcMinsInHour(@start DATETIME, @end DATETIME, @hour INTEGER)
RETURNS INTEGER 
AS
BEGIN
    DECLARE @StartOfHour DATETIME
    DECLARE @EndOfHour DATETIME
    SELECT @StartOfHour = DATEADD(hh, @hour, CAST(CAST(@start AS DATE) AS DATETIME))
    IF NOT (@StartOfHour BETWEEN @start and @end)
        SET @StartOfHour = DATEADD(hh, @hour, CAST(CAST(@end AS DATE) AS DATETIME))

    SELECT @EndOfHour = DATEADD(hh, 1, @StartOfHour)

RETURN
(
SELECT 
    CASE WHEN @EndOfHour < @start OR @StartOfHour > @end THEN 0 ELSE
        DATEDIFF(mi,
            CASE WHEN @StartOfHour <= @start THEN @start ELSE @StartOfHour END,
            CASE WHEN @EndOfHour > @end THEN @end ELSE @EndOfHour END)  
    END
)
END

edited Jan 18 '12 at 20:45

answered Jan 18 '12 at 20:19

AdaTheDev

142,592
28
206
200

+1 - much more elegant than my bazillion `CASE` statement solution I'm not done with yet :) – JNK Jan 18 '12 at 20:22
this does not work in case the interval is '2012-01-18 20:15' - '2012-01-19 2:30' and the hour is 2, for instance – Cristian Lupascu Jan 18 '12 at 20:28
Awesome, you rock. I'll buy your lunch if you're ever in Dallas :) – Brandon Moore Jan 18 '12 at 20:30
@w0lf True. I'm working on rectifying that part now. I will post the final answer if AdaTheDev or someone else doesn't fix it before I do... – Brandon Moore Jan 18 '12 at 20:33
True, it's because it assumes you are talking about the given hour on the START date. The question is, how can it know which 2 o'clock you are talking about? This is even murkier if the start and end dates are even more days apart. – AdaTheDev Jan 18 '12 at 20:38
@AdaTheDev - Brandon said he wanted this for a single day - I think he meant a single 24 hour interval, so the hour can appear only once in the interval. You could just replicate the select you made for the day after and add that to the result - it would be the simplest solution I think – Cristian Lupascu Jan 18 '12 at 20:43
OK, read @BrandonMoore's edit in the Q. re: never more than a 24 hour interval. I've updated my answer which now works with that extra scenario - it will start off basing it on the Start date, and fall back to the End date if it deems appropriate. How does that look? – AdaTheDev Jan 18 '12 at 20:46

score 1 · Answer 2 · answered Jan 18 '12 at 19:55

1

Can you use DateDiff?

select DATEDIFF(Minute, '2011-11-10 00:00:59.900', '2011-11-10 00:01:00.100')

answered Jan 18 '12 at 19:55

JonH

32,732
12
87
145

I think it's going to have to be a case statement with multiple DateDiff's. Idea is to get a sum of labor hours worked for each hour in the day. – Brandon Moore Jan 18 '12 at 20:08

Cristian Lupascu · Answer 3 · 2012-01-18T20:24:29.427

Here's the function that calculates the total number of minutes (also works with multiple days):

create function CalcMinsInHour(@start datetime, @end datetime, @hour int)
    returns int
as
begin

    declare @selecterHourIntervals table (HourStart datetime, HourEnd datetime)
    declare @currentDate datetime

    /* start in the @start date, but the hour we want to count */
    set @currentDate = dateadd(hour, @hour, convert(datetime, convert(date, @start)))

    /* for every day between @start-@end add out hour interval */
    while @currentDate <= @end
    begin 
        insert into @selecterHourIntervals values (
                        @currentDate, 
                        dateadd(hour, 1, @currentDate))

        set @currentDate = dateadd(day, 1, @currentDate)
    end

    declare @totalMinutes int

    /* for every hour interval in the table, select number of minutes 
       trimmed by the @start - @end interval and sum */
    select @totalMinutes = sum(DailyMinutes)
    from
        (select
            datediff(minute,
            case when HourStart > @start then HourStart else @start end,
            case when HourEnd < @end then HourEnd else @end end) as DailyMinutes
        from @selecterHourIntervals) TrimmedIntervals
    where DailyMinutes > 0

    return isnull(@totalMinutes, 0)

end

That is, I realize this answer could be more 'correct' than AdaTheDev's, but I really wanted a solution without loops, temp tables, etc. that would slow it down... but definitely want to give you +1 for the effort! — Brandon Moore, Jan 18 '12 at 20:41
@BrandonMoore - I know, AdaTheDev's solution is indeed a lot more elegant and can be slightly adjusted to yield the correct result — Cristian Lupascu, Jan 18 '12 at 20:45

Calculate how many minutes used in a given hour

3 Answers3

Linked