Calculate time difference (only working hours) in minutes between two dates

I need to calculate the number of "active minutes" for an event within a database. The start-time is well known.

The complication is that these active minutes should only be counted during a working day - Monday-Friday 9am-6.30pm, excluding weekends and (known) list of holiday days

The star开发者_如何学JAVAt or "current" time may be outside working hours, but still only the working hours are counted.

This is SQL Server 2005, so T-SQL or a managed assembly could be used.

If you want to do it pure SQL here's one approach

CREATE TABLE working_hours (start DATETIME, end DATETIME);

Now populate the working hours table with countable periods, ~250 rows per year.

If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query

SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end

will suffice.

If on the other hand the event starts and/or ends during working hours the query is more complicated

SELECT SUM(duration) 
FROM 
(
   SELECT SUM(end-start) as duration
   FROM working_hours
   WHERE start >= @event_start AND end <= @event_end
UNION ALL
   SELECT end-@event_start
   FROM working_hours
   WHERE @event_start between start AND end
UNION ALL
   SELECT @event_end - start
   FROM working_hours
   WHERE @event_end between start AND end
) AS u

Notes:

• the above is untested query, depending on your RDBMS you might need date/time functions for aggregating and subtracting datetime (and depending on the functions used the above query can work with any time precision).
• the query can be rewritten to not use the UNION ALL.
• the working_hours table can be used for other things in the system and allows maximum flexibility

EDIT: In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.

Using unreason's excellent starting point, here is a TSQL implementation for SQL Server 2012.

This first SQL populates a table with our work days and times excluding weekends and holidays:

declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = '2016-01-01'
Select @dteEnd = '2016-12-31'

CREATE TABLE working_hours (starttime SMALLDATETIME, endtime SMALLDATETIME);

while @dteStart <= @dteEnd
BEGIN
   IF    datename(WEEKDAY, @dteStart) <> 'Saturday' 
     AND DATENAME(WEEKDAY, @dteStart) <> 'Sunday'
     AND @dteStart not in ('2016-01-01' --New Years
                          ,'2016-01-18' --MLK Jr
                          ,'2016-02-15' --President's Day
                          ,'2016-05-30' --Memorial Day
                          ,'2016-07-04' --Fourth of July
                          ,'2016-09-05' --Labor Day
                          ,'2016-11-11' --Veteran's Day
                          ,'2016-11-24' --Thanksgiving
                          ,'2016-11-25' --Day after Thanksgiving
                          ,'2016-12-26' --Christmas
                          )
      BEGIN
        select @dtStart = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),8,0) --8:00am
        select @dtEnd   = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),17,0) --5:00pm
        insert into working_hours values (@dtStart,@dtEnd)
      END
   Select @dteStart = DATEADD(day,1,@dteStart)
END

Now here is the logic that worked to return the minutes as an INT:

declare @event_start datetime2
declare @event_end datetime2
select @event_start = '2016-01-04 8:00'
select @event_end = '2016-01-06 16:59'

SELECT SUM(duration) as minutes
FROM 
(
   SELECT DATEDIFF(mi,@event_start,@event_end) as duration
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end <= endtime
UNION ALL
   SELECT DATEDIFF(mi,@event_start,endtime)
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end > endtime
UNION ALL
   SELECT DATEDIFF(mi,starttime,@event_end)
   FROM working_hours
   WHERE @event_end >= starttime 
     AND @event_end <= endtime
     AND @event_start < starttime
UNION ALL
   SELECT SUM(DATEDIFF(mi,starttime,endtime))
   FROM working_hours
   WHERE starttime > @event_start
     AND endtime < @event_end
) AS u

This correctly returns 1 minute shy of three 9 hour work days

I came here looking for an answer to a very similar question - I needed to get the minutes between 2 dates excluding weekends and excluding hours outside of 08:30 and 18:00. After a bit of hacking around, I think i have it sorted. Below is how I did it. thoughts are welcome - who knows, maybe one day I'll sign up to this site :)

create function WorkingMinutesBetweenDates(@dteStart datetime, @dteEnd datetime)
returns int
as
begin

declare @minutes int
set @minutes = 0

while @dteEnd>=@dteStart
    begin

        if  datename(weekday,@dteStart) <>'Saturday' and  datename(weekday,@dteStart)<>'Sunday'
            and (datepart(hour,@dteStart) >=8 and datepart(minute,@dteStart)>=30 )
            and (datepart(hour,@dteStart) <=17)

            begin
                set @minutes = @minutes + 1
            end     

        set @dteStart = dateadd(minute,1,@dteStart)
    end

return @minutes
end
go

I started working with what Unreason posted and was a great start. I tested this is SQL Server and found not all time was being captured. I think the problem was primarily when the event started and ended the same day. This solution seems to be working well enough for me

CREATE TABLE [dbo].[working_hours](
[wh_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[wh_starttime] [datetime] NULL,
[wh_endtime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
[wh_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,           ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE FUNCTION [dbo].[udFWorkingMinutes] 
(
@startdate DATETIME
,@enddate DATETIME
)
RETURNS INT
AS
BEGIN


DECLARE @WorkingHours INT
SET @WorkingHours = 
(SELECT 
CASE WHEN COALESCE(SUM(duration),0) < 0 THEN 0 ELSE SUM(Duration) 
END AS Minutes
FROM 
(
    --All whole days
   SELECT ISNULL(DATEDIFF(mi,wh_starttime,wh_endtime),0) AS Duration
   FROM working_hours
   WHERE wh_starttime >= @startdate AND wh_endtime <= @enddate 
   UNION ALL
   --All partial days where event start after office hours and finish after office hours
   SELECT ISNULL(DATEDIFF(mi,@startdate,wh_endtime),0) AS Duration
   FROM working_hours
   WHERE @startdate > wh_starttime AND @enddate >= wh_endtime 
   AND (CAST(wh_starttime AS DATE) = CAST(@startdate AS DATE))
   AND @startdate < wh_endtime
   UNION ALL
   --All partial days where event starts before office hours and ends before day end
   SELECT ISNULL(DATEDIFF(mi,wh_starttime,@enddate),0) AS Duration
   FROM working_hours
   WHERE @enddate < wh_endtime 
   AND @enddate >= wh_starttime
   AND @startdate <= wh_starttime 
   AND (CAST(wh_endtime AS DATE) = CAST(@enddate AS DATE))
   UNION ALL  
    --Get partial day where intraday event
   SELECT ISNULL(DATEDIFF(mi,@startdate,@enddate),0) AS Duration
   FROM working_hours
   WHERE @startdate > wh_starttime AND @enddate < wh_endtime 
   AND (CAST(@startdate AS DATE)= CAST(wh_starttime AS DATE))
   AND (CAST(@enddate AS DATE)= CAST(wh_endtime AS DATE))
 ) AS u)

 RETURN @WorkingHours
END
GO

Alls that is left to do is populate the working hours table with something like

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,'2014-01-01 9:00:00 AM') = 1 THEN '2014-01-01 9:00:00 AM' 
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 9:00:00 AM')+1,0) END AS      myStartDate,
CASE WHEN DATEPART(Day,'2014-01-01 5:00:00 PM') = 1 THEN '2014-01-01 5:00:00 PM' 
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 5:00:00 PM')+1,0) END AS myEndDate
UNION ALL
SELECT DATEADD(Day,1,myStartDate), DATEADD(Day,1,myEndDate)
FROM cte
WHERE DATEADD(Day,1,myStartDate) <=  '2015-01-01'
)
INSERT INTO working_hours
SELECT myStartDate, myEndDate
FROM cte
OPTION (MAXRECURSION 0)

delete from working_hours where datename(dw,wh_starttime) IN ('Saturday', 'Sunday')

--delete public holidays

delete from working_hours where CAST(wh_starttime AS DATE) = '2014-01-01'

My first post! Be merciful.

Globally, you'd need:

1. A way to capture the end-time of the event (possibly through notification, or whatever started the event in the first place), and a table to record this beginning and end time.
2. A helper table containing all the periods (start and end) to be counted. (And then you'd need some supporting code to keep this table up to date in the future)
3. A stored procedure that will:
   • iterate over this helper table and find the 'active' periods
   • calculate the minutes within each active period.

(Note that this assumes the event can last multiple days: is that really likely?)

A different method would be to have a ticking clock inside the event, which checks every time whether the event should be counted at that time, and increments (in seconds or minutes) every time it discovers itself to be active during the relevant period. This would still require the helper table and would be less auditable (presumably).