Calculating Business Hours
I was recently tasked with generating SSRS reports against a Microsoft System Center Service Manager data warehouse. The reports require that only business hours be used to extract things like how long a service management ticket was worked etc. In my case the service desk is open M-F from 7:30 to 4:30 and no weekends or Federal Holidays. I expected to find a number of good examples with a few searches but nothing came up that fit my needs exactly.
Here's a quick rundown of how I was able to satisfy the reporting requirements.
First - create a new table to store the work week hours including Saturday and Sunday, this table will be used whenever a query is generated to determine how many hours fall within the "open" hours of the service desk.
I started the process with some examples I gathered here - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645 I then extended the examples to include Holidays and fix the queries when the start date and end date were on the same day.
Open SQL Management Studio and attach to the database you wish to add this new table to, paste the following SQL code into the query and execute.
CREATE TABLE [dbo].[ttr_calendar] (
[day_number] [varchar] (50) NOT NULL ,
[day_name] [varchar] (50) NULL ,
[begin_time] [datetime] NULL ,
[end_time] [datetime] NULL ,
[duration] [real] NULL
) ON [PRIMARY]
Next I'll populate the table with the hours of the service desk. Execute the following statement
insert into ttr_calendar
select 1, 'Monday', '7:30:00 AM', '4:30:00 PM', 32400 union all
select 2, 'Tuesday', '7:30:00 AM', '4:30:00 PM', 32400 union all
select 3, 'Wednesday', '7:30:00 AM', '4:30:00 PM', 32400 union all
select 4, 'Thursday', '7:30:00 AM', '4:30:00 PM', 32400 union all
select 5, 'Friday', '7:30:00 AM', '4:30:00 PM', 32400 union all
select 6, 'Saturday', '7:30:00 AM', '4:30:00 PM', 0 union all
select 7, 'Sunday', '7:30:00 AM', '4:30:00 PM', 0
For brevity I'll just link the F_Table_Date function that I used in conjunction with the other tables, this code will need to be copied and executed on our SQL instance, this will generate a function that is used in the query.
Using F_Table_Date - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE
Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. In short, it’s a calendar table function.
Next I need to create a table to store the Holiday information - Note: This table will have to be manually populated with your holiday information, simply add a title i.e. Christmas, New Years, etc. and the date in the following format 12/25/2012.
USE [DWDataMart] /* <--Your Database Name */
GO
/****** Object: Table [dbo].[ttr_Holiday] Script Date: 05/22/2012 14:37:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ttr_Holiday](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Date] [date] NOT NULL,
CONSTRAINT [PK_ttr_Holiday] PRIMARY KEY CLUSTERED
(
[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
SET ANSI_PADDING OFF
GO
Now that all the pieces are in place I can generate the actual query that will return my business hours minus Holidays.
declare @start_date datetime,
@end_date datetime,
@temp int
select @start_date = '24 May 2012 07:30:00 AM',
@end_date = '29 May 2012 04:30:00 PM'
Select @temp = COUNT(ttr_holiday.date)*9 from ttr_holiday where ttr_holiday.date Between @start_date and @end_date
select @temp, total_hours = sum(case
when dateadd(day, datediff(day, 0, @start_date), 0) = dateadd(day, datediff(day, 0, @end_date), 0) then
CASE
WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'
THEN
datediff(second, [DATE] + begin_time, [DATE] + end_time)
WHEN CONVERT(VARCHAR(8),@start_date,108) < '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'
THEN
datediff(second, [DATE] + begin_time, @end_date)
WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) < '16:30:00'
THEN
datediff(second, @start_date, @end_date)
WHEN CONVERT(VARCHAR(8),@start_date,108) > '07:30:00 AM' and CONVERT(VARCHAR(8),@end_date,108) > '16:30:00'
THEN
datediff(second, @start_date, [DATE] + end_time)
END
when [DATE] = dateadd(day, datediff(day, 0, @start_date), 0) then
case
when @start_date > [DATE] + begin_time then datediff(second, @start_date, [DATE] + end_time)
else duration
end
when [DATE] = dateadd(day, datediff(day, 0, @end_date), 0) then
case
when @end_date < [DATE] + end_time then datediff(second, [DATE] + begin_time, @end_date)
else duration
end
else duration
end
)
/ 60.0 / 60.0 - @temp
from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
This query will also take into account the start date and end date being on the same day, in several examples that I looked at this part would not return the correct results.
I hope this post helps with calculating business hours.
Neat solution. Thanks for sharing!
ReplyDelete