USE [ProcessManager] GO /****** Object: UserDefinedFunction [dbo].[GetNewBusinessHours] Script Date: 12/8/2017 7:52:54 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[GetNewBusinessHours] ( @sessionid nvarchar(40) ='00000000-0000-0000-0000-000000000000' ) returns @BizHours TABLE ( StartTime DateTime, EndTime DateTime ) as begin declare @bStartHour varchar(2) declare @bEndHour varchar(2) declare @bhd_xml xml set @bhd_xml = ( select CONVERT(xml, bh.BusinessHoursData) from BusinessHours bh join SLAConfig slac on slac.BusinessHoursID = bh.BusinessHoursID join SLAProcess slap on slap.SLAConfigID = slac.SLAConfigID join SLAMilestone slam on slam.SLAMilestoneID = slac.SLAMilestoneID join ReportProcess rp on rp.ReportLogProcessID = slap.ReportLogProcessID where slam.MilestoneStatus = 'Initial Response' and rp.SessionID = @sessionid ) select @bStartHour = RIGHT('00'+ISNULL(b.value('./Object[4]/Values[1]/Value[1]/@val', 'varchar(2)'),''), 2), @bEndHour = RIGHT('00'+ISNULL(b.value('./Object[5]/Values[1]/Value[1]/@val', 'varchar(2)'),''), 2) from @bhd_xml.nodes('/ObjectStorageContainer/References') as a(b) INSERT @BizHours SELECT replace(convert(varchar, getdate(), 111), '/', '-') + ' ' + @bStartHour + ':00:00.000', replace(convert(varchar, getdate(), 111), '/', '-') + ' ' + @bEndHour + ':00:00.000' return end GO