Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Guidance on creating measures for duration of time

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
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question