IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'TF' AND name = 'ComTrade_XenDesktop_GetDatesTrend')
BEGIN
EXECUTE ('CREATE FUNCTION dbo.[ComTrade_XenDesktop_GetDatesTrend]() RETURNS @Result TABLE ("DateTime" datetime, "Trend" float) AS BEGIN RETURN END')
END
GO
ALTER FUNCTION [dbo].[ComTrade_XenDesktop_GetDatesTrend]
(
@StartDateTrend datetime,
@EndDateTrend datetime,
@m_A float,
@m_b float,
@minDate datetime,
@AggregationType int,
@BusinessTimeType int,
@BusinessDay1 int,
@BusinessDay2 int,
@BusinessDay3 int,
@BusinessDay4 int,
@BusinessDay5 int,
@BusinessDay6 int,
@BusinessDay7 int,
@BH_start datetime,
@BH_end datetime
)
RETURNS @Result TABLE ("DateTime" datetime, "Trend" float)
AS
BEGIN
DECLARE @IncDateTrend datetime
DECLARE @IncDateTrend2Float float
SET @IncDateTrend = DATEADD(hour,+1,(@StartDateTrend))
WHILE (@IncDateTrend < @EndDateTrend)
BEGIN
IF @BusinessTimeType = 1 --BUSINESS HOURS
BEGIN
IF @IncDateTrend - CAST(FLOOR(CAST(@IncDateTrend AS float)) AS datetime) >= @BH_start
and @IncDateTrend - CAST(FLOOR(CAST(@IncDateTrend AS float)) AS datetime) < @BH_end
and ((DATEPART(WEEKDAY, @IncDateTrend) = 1 and @BusinessDay1 = 1) --monday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 2 and @BusinessDay2 = 1) --tuesday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 3 and @BusinessDay3 = 1) --wednesday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 4 and @BusinessDay4 = 1) --thursday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 5 and @BusinessDay5 = 1) --friday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 6 and @BusinessDay6 = 1) --saturday
OR (DATEPART(WEEKDAY, @IncDateTrend) = 7 and @BusinessDay7 = 1)) --sunday
BEGIN
SET @IncDateTrend2Float = cast((@IncDateTrend - DATEADD(day,-1,(@minDate))) as float)
INSERT INTO @Result ([DateTime], [Trend]) VALUES (@IncDateTrend,
CASE WHEN @m_A + (@m_b * @IncDateTrend2Float) < 0 THEN 0.000000
ELSE @m_A + (@m_b * @IncDateTrend2Float) END)
END
END
ELSE --NO BUSINESS HOURS
BEGIN
SET @IncDateTrend2Float = cast((@IncDateTrend - DATEADD(day,-1,(@minDate))) as float)
INSERT INTO @Result ([DateTime], [Trend]) VALUES (@IncDateTrend,
CASE WHEN @m_A + (@m_b * @IncDateTrend2Float) < 0 THEN 0.000000
ELSE @m_A + (@m_b * @IncDateTrend2Float) END)
END
SET @IncDateTrend = DATEADD(hour,+1,(@IncDateTrend))
END
RETURN
END
GO
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT SELECT ON dbo.[ComTrade_XenDesktop_GetDatesTrend] TO OpsMgrReader
GO
Home