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...
Mark Prosser 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