Those snippets are directly extracted from the stored procedure in its before and after state.
Here is the function
if exists (select * from sysobjects where name='fn_GetTargetTimeInServiceHours' and type='FN')
drop function fn_GetTargetTimeInServiceHours
go
/*----------------------------------------------------------------------------------------------------------------
fn_GetTargetTimeInServiceHours - used to add/subtract a number of months from a integer year and month, this can return
either the year or the month
- pass in: project_id, folder_id, start date/time (GMT), time (in seconds)
- returns: the actual target date
@project_id - integer value that represents the project in question
@folder_id - integer value that represents the folder service hours to use
@start_date_time - start date time to calculate @time_in_secs from
@time_in_secs - Time in seconds to determine the target date/time in reference to the start_date_time
based on the folder service hours. Note, this time should include any calculated hold
time that you want to factor into this.
----------------------------------------------------------------------------------------------------------------*/
CREATE FUNCTION fn_GetTargetTimeInServiceHours(@project_id int, @folder_id int, @start_date_range datetime, @time_in_secs int)
RETURNS datetime
AS
BEGIN
declare @target_date_time datetime, @GMT_now datetime, @service_fragment int
if @start_date_range is null
begin
exec spg_get_gmt_now @GMT_now = @GMT_now output
return (@GMT_now)
end
else
if (@project_id is null or @folder_id is null or @start_date_range is null or @time_in_secs is null)
return (@start_date_range)
/*---------------------------------------------------------------------------------------------
If no service hours simply return the start date time + the time_in_secs.
---------------------------------------------------------------------------------------------*/
if not exists (select 1 from service_hour sh (nolock) where sh.project_id = @project_id and sh.folder_id = @folder_id
and sh.start_time >= @start_date_range and sh.start_time <= dateadd(mm, 1, @start_date_range))
begin
select @target_date_time = DATEADD(s, @time_in_secs, @start_date_range)
end
if not @target_date_time is null
return (@target_date_time)
else
begin
select @target_date_time = @start_date_range
while @time_in_secs > 0
begin
select @service_fragment = 0
if exists (select 1 from service_hour sh (nolock)
where sh.project_id = @project_id
and sh.folder_id = @folder_id
and @target_date_time >= sh.start_time
and @target_date_time <= DATEADD(s, sh.service_seconds, sh.start_time)
and sh.service_seconds > 0)
begin
select top 1 @service_fragment = datediff(ss, @target_date_time, DATEADD(s, sh.service_seconds, sh.start_time))
from service_hour sh (nolock)
where sh.project_id = @project_id
and sh.folder_id = @folder_id
and @target_date_time >= sh.start_time
and @target_date_time <= DATEADD(s, sh.service_seconds, sh.start_time)
and sh.service_seconds > 0
if @time_in_secs > @service_fragment
select @target_date_time = dateadd(ss, @service_fragment + 1, @target_date_time),
@time_in_secs = @time_in_secs - @service_fragment
else
if @time_in_secs < @service_fragment
select @target_date_time = dateadd(ss, @time_in_secs, @target_date_time),
@time_in_secs = 0
else
select @target_date_time = dateadd(ss, @service_fragment, @target_date_time),
@time_in_secs = 0
end
else
begin
/*---------------------------------------------------------------------------------------------
Check to see if we have run out of service hours
---------------------------------------------------------------------------------------------*/
if exists (select 1 from service_hour sh (nolock)
where sh.project_id = @project_id
and sh.folder_id = @folder_id
and sh.start_time > @target_date_time
and sh.start_time <= DATEADD(mm, 1, @target_date_time)
and sh.service_seconds > 0)
/*---------------------------------------------------------------------------------------------
Move target_date_time to beginning of next available service day
---------------------------------------------------------------------------------------------*/
select @target_date_time = min(sh.start_time)
from service_hour sh (nolock)
where sh.project_id = @project_id
and sh.folder_id = @folder_id
and sh.start_time > @target_date_time
and sh.start_time <= DATEADD(mm, 1, @target_date_time)
and sh.service_seconds > 0
else
begin
/*---------------------------------------------------------------------------------------------
Service Hours have run out, add remaining time assuming 24x7 service hours
---------------------------------------------------------------------------------------------*/
select @target_date_time = dateadd(ss, @time_in_secs, @target_date_time)
select @time_in_secs = 0
end
end
end
end
return(@target_date_time)
END
go
grant execute on fn_GetTargetTimeInServiceHours to public
go
Here is the temp table definition
create table #request_meet_criteria (
project_id int not null,
request_id int not null,
folder_id int,
category_code char(5) collate database_default,
subcategory_code char(5) collate database_default,
module_id int,
resolution_id int,
opened_date datetime,
include_task_id int not null,
include_date datetime not null,
sla_start_date datetime,
sla_target_date datetime,
sla_complete_date datetime,
consumed_time int not null,
hold_time int not null,
sla_consumed_time int not null,
target_time int not null,
smx_active_flag int,
met_sla char(1) collate database_default not null
)