Guidance on creating measures for duration of time

[Former Member]

I'm struggling a bit on how best to create measures in BO3.1 Unnivers for reporting with WEBI. I have two dates and need to calculate the time in minutes between the two dates. The real challange for me is the duration must be based on business hours (9 to 5) and exclude weekends and holidays. I've done similar things with crystal but at lost on how to get started with the universe designer. Any help would be greatly appreciated.

Thanks, old mainframe programmer...

[Former Member · Accepted solution]

What if it is out of working hours... how do you wish to handle that

[Former Member · Accepted solution]

Thanks! for your reply. I need to calculate the duration in hours between the two dates by only adding the time for each day between the dates if it falls on a work day (mon-fri, excluding weekend and hoildays) and hours between 9 and 5. A scenario would be for a Help Desk. Managements want to track help desk agents performance (elapse time to resolve issue). The elapse time is only counts if it falls during the 9-5 business days.

Again, thanks very much in your interest to help.

[Former Member · Accepted solution]

Scott,

I've done something similar.

You're looking at a database level solution because it's going to be too complex for a basic select statement.

Google turned up some examples that I tweaked.

What is your datasource?

[Former Member · Accepted solution]

My datasource is either MS SQL Sever or Oracle rdbms. I'm working with an enterprise/commercial help desk app. It has an activity table that stores data-time stamps as unix time (seconds from 1970). I have a BO3.1 universe that maps the entire app schema and has object that provide "open data", "closed date", and "duration in day from Open to Close" The duration calculation is for a 24hour clock. I need to calculate the duration from open to close and only count time between 9 & 5 and on work days (m-f).

Example: Ticket is opened on friday at 4:00pm and closed on Monday at 1:00pm, what I have noon is 69 hours for the duration. I need a calcaltion that comes up with 5 hours. I know it sounds complex to me. I'm a little surprised I have found anything. It seem like a common requirement for business system reporting.

Thanks Much for you help! I hope i explained my problem clearly.

Scott

[Former Member · Accepted solution]

I've built two of these.

Have a play with this one - it's a function in SQL Server to get the resolution time rather than the elapsed time.

I had a calendar table that has a working day flag - includes Easter, Christmas, etc. You'll need to tweak your code -like changing the dates, etc. but it's a decent starter for you to complete your job with.

CREATE FUNCTION [dbo].[F_GET_RESOLVED_HOURS](@woid int) returns decimal(15,2) AS

BEGIN

DECLARE @startdate DATETIME --opendate
DECLARE @enddate  DATETIME --closeddate
DECLARE @startdatetime DATETIME --opendate inc time
DECLARE @enddatetime  DATETIME --closeddate inc time

SELECT @startdatetime = OPENDATE from TASKS where woid = @woid
SELECT @enddatetime = CLSDDATE from TASKS where woid = @woid
SELECT @startdate = cast(convert(char(10),@startdatetime,23) as datetime)
SELECT @enddate = cast(convert(char(10),@enddatetime,23) as datetime) 

DECLARE @singleday bit
SELECT @singleday = CASE WHEN @startdate = @enddate THEN 1 ELSE 0 END

DECLARE @hours decimal(15,2)
DECLARE @workday int

declare @dayend int
declare @daystart int
declare @callstart int
declare @callend int

select @dayend = 1080 -- 18:00 in minutes from 00:00
select @daystart = 480 -- 8:00 in minutes from 00:00

select @callstart = datediff(mi,@startdate,@startdatetime) -- get the minutes from 00:00 that day.
select @callstart = CASE WHEN @callstart < @daystart THEN @daystart WHEN @callstart > @dayend THEN @dayend ELSE @callstart END

select @callend = datediff(mi,@enddate,@enddatetime) -- get the minutes from 00:00 that day.
select @callend = CASE WHEN @callend < @daystart THEN @daystart WHEN @callend > @dayend THEN @dayend ELSE @callend END

select @hours = (@callend - @callstart)/60.00
IF @singleday = 1 GOTO ENDER

declare @minsdone int
declare @workdaymins int
select @workdaymins = @dayend - @daystart

select @minsdone = 0
select @minsdone = @dayend - @callstart
select @minsdone = @minsdone + (@workdaymins *SUM(working_day_flag)) from U_CALENDAR
where calendar_date between (@startdate+1) and (@enddate-1)
select @minsdone = @minsdone + @callend - @daystart

select @hours = @minsdone/60.00
ENDER:
select @hours = COALESCE(@hours,0.00)
RETURN @hours

END
GO