IF (OBJECTPROPERTY(OBJECT_ID('sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet]'), 'IsProcedure') IS NULL)
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate datetime
,@EndDate datetime
,@AggregationTypeId int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@Statement nvarchar(max)
-- adjust date & time inline with availability report
-- exclude minutes, seconds, milliseconds
-- and add 1 hour in case of daily aggregations
SELECT
@StartDate = DATEADD(hh, DATEPART(hh, @StartDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @StartDate, 112))
,@EndDate = DATEADD(hh, DATEPART(hh, @EndDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @EndDate, 112))
+ ' SELECT'
+ ' slome.ServiceLevelObjectiveRowId'
+ ' ,slome.ManagementGroupRowId'
+ ' ,slome.ServiceLevelObjectiveManagedEntityRowId'
+ ' ,p.DateTime'
+ ' ,CASE pslompv.AggregationMethod'
+ ' WHEN ''min'' THEN p.MinValue'
+ ' WHEN ''avg'' THEN p.AverageValue'
+ ' WHEN ''max'' THEN p.MaxValue'
+ ' END '
+ ' ,p.SampleCount'
+ ' ,NULL'
+ ' FROM #ServiceLevelObjectiveManagedEntity slome'
+ ' JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)'
+ ' JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)'
+ ' JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = pslompv.ServiceLevelObjectiveManagementPackVersionRowId)'
+ ' JOIN vPerformanceRuleInstance pri ON (pslompv.RuleRowId = pri.RuleRowId)'
+ ' JOIN %PerfDataTable% p ON (pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId) AND (slome.ServiceLevelObjectiveManagedEntityRowId = p.ManagedEntityRowId)'
+ ' WHERE(p.[DateTime] >= @StartDate)'
+ ' AND (p.[DateTime] < @EndDate)'
+ ' AND NOT EXISTS (SELECT *'
+ ' FROM vServiceLevelObjectiveManagementPackVersion slompv_newer'
+ ' JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)'
+ ' WHERE (slompv_newer.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)'
+ ' AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)'
+ ' AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)'
+ ' )'
IF (@AggregationTypeId = 20)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', '[vStateHourlyFull]')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfHourly]')
END
ELSE IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', '[vStateDailyFull]')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfDaily]')
END