IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSLADetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetSLADetails]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServiceLevelFailures]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStateInterval]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet_60Minutes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet_60Minutes]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServiceLevelFailures]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetServiceLevelFailures]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetSLADetails]
    @LanguageCode            varchar(10)
AS
BEGIN
    -- This stored procedure will return all Service Level details along with Management Group and ServiceLevelTargetManaged Entity.
    SET NOCOUNT ON
    DECLARE 
        @ErrorInd                    bit
        ,@ErrorMessage               nvarchar(max)
        ,@ErrorNumber                int
        ,@ErrorSeverity              int
        ,@ErrorState                 int
        ,@ErrorLine                  int
        ,@ErrorProcedure             nvarchar(256)
        ,@ErrorMessageText           nvarchar(max)
        ,@ManagementGroups           XML
        ,@StartDate                  Datetime
    
    BEGIN TRY

    SET @StartDate = GETUTCDATE()

    -- Get all Management Groups
    SET @ManagementGroups=(SELECT ManagementGroupGuid  as ManagementGroup from vmanagementgroup
    FOR XML PATH(''), TYPE, ROOT('Root'));

    EXEC ServiceLevelAgreementSearch @ManagementGroupXml=@ManagementGroups,@FromDateTime= @StartDate, @SearchPattern=N'%%',@LanguageCode=@LanguageCode
        
    END TRY
    BEGIN CATCH
        SELECT 
            @ErrorNumber = ERROR_NUMBER()
            ,@ErrorSeverity = ERROR_SEVERITY()
            ,@ErrorState = ERROR_STATE()
            ,@ErrorLine = ERROR_LINE()
            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
            ,@ErrorMessageText = ERROR_MESSAGE()
        
        SET @ErrorInd = 1
    END CATCH

Cleanup:
   
  -- report error if any
  IF (@ErrorInd = 1)
  BEGIN
    DECLARE @AdjustedErrorSeverity INT
    SET @AdjustedErrorSeverity = CASE WHEN @ErrorSeverity > 18 THEN 18 ELSE @ErrorSeverity END
    
    RAISERROR (777971002, @AdjustedErrorSeverity, 1
      ,@ErrorNumber
      ,@ErrorSeverity
      ,@ErrorState
      ,@ErrorProcedure
      ,@ErrorLine
      ,@ErrorMessageText
    )
  END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetServiceLevelFailures] 
    @mgCollectionXML XML
    ,@StartDate    DateTime
    ,@EndDate    DateTime
    ,@AggregationTypeId INT
    ,@LanguageCode VARCHAR(10)
AS
BEGIN
    -- GetServiceLevelFailures '<METRICS><MG Id="1"><SLA Id="1"><SLAME Id="11"><SLO Id="2" /><SLO Id="1"><SLOME Id="11" /></SLO></SLAME><SLAME Id="12"><SLO Id="2" /><SLO Id="1" /></SLAME></SLA></MG></METRICS>', '2009-02-15 20:28:57.130', '2009-02-16 20:28:57.130', 'ENU'

    DECLARE 
        @XmlDocHandle int
        ,@ExecResult int
        ,@ResultXml XML
        ,@UseAggregatesToBuildStartOfIntervalStateInd INT
        ,@AggregationCoverViewName sysname
        ,@PreviousIntervalStartDateTime datetime
        ,@DatasetId UNIQUEIDENTIFIER

    IF (@AggregationTypeId = 20 or @AggregationTypeId = 30)
    BEGIN
    -- 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 30 THEN 1 ELSE 0 END, convert(varchar(8), @StartDate, 112))
            ,@EndDate = DATEADD(hh, DATEPART(hh, @EndDate) + CASE @AggregationTypeId WHEN 30 THEN 1 ELSE 0 END, convert(varchar(8), @EndDate, 112))
            ,@PreviousIntervalStartDateTime = DATEADD(minute, -ABS(DATEDIFF(minute, @StartDate, @EndDate)), @StartDate)
    END
    ELSE
    BEGIN
        SELECT 
            @StartDate = CONVERT(SMALLDATETIME, @StartDate)
            ,@EndDate = CONVERT(SMALLDATETIME, @EndDate)
    END

    -- TODO: Need to find the right way.
    SET @DatasetId = (SELECT DataSetId FROM vDataSet WHERE DatasetDefaultName = 'State data set')

    -- process mg Collection XML
    EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @mgCollectionXML

    IF OBJECT_ID('tempdb..#StateEvent') IS NOT NULL
    BEGIN
        DROP TABLE #StateEvent
    END

    -- State Event table for storing the health state changes for all ManagedEntityMonitorRowId.
    CREATE TABLE #StateEvent (
     ManagedEntityMonitorRowId int
    ,[DateTime]           datetime
    ,[LocalTime]          datetime
    ,OldHealthState       tinyint
    ,NewHealthState       tinyint
    )

    CREATE TABLE #SLDetails
    (
        MgId    INT,
        SLAId    INT,
        SLAMEId    INT,
        SLOId    INT,
        SLOMEId    INT        
    )

    INSERT INTO #SLDetails (MgId, SLAId, SLAMEId, SLOId, SLOMEId)
    SELECT 
        x.value ('../../../../@Id', 'INT') as MGId,
        x.value ('../../../@Id', 'INT') as SLAId,
        x.value ('../../@Id', 'INT') as SLAMEId,
        x.value ('../@Id', 'INT') as SLOId,
        x.value ('@Id','INT') as SLOMEId
    FROM  @mgCollectionXML.nodes('/METRICS/MG/SLA/SLAME/SLO/SLOME') as R(x)

    CREATE TABLE #ManagedEntityMonitor
    (
        ManagedEntityMonitorRowId INT
    )

    -- Populate on the required ManagedEntityMonitors before making a call to StateChangeEventList.
    INSERT INTO #ManagedEntityMonitor(ManagedEntityMonitorRowId)
    SELECT distinct
                mem.ManagedEntityMonitorRowId
    FROM #SLDetails sldetails
            JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = sldetails.SLOId)
            JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = sldetails.MGId)
            JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = mslompv.ServiceLevelObjectiveManagementPackVersionRowId)
            JOIN vManagedEntityMonitor mem ON (sldetails.SLOMEId = mem.ManagedEntityRowId) AND (mem.MonitorRowId = mslompv.MonitorRowId)

      -- load states for monitors that were
      -- not green at the beginning of this period
      IF EXISTS (SELECT * FROM StandardDatasetAggregationHistory
                 WHERE (AggregationCount > 0)
                   AND (AggregationTypeId = @AggregationTypeId)
                   AND (AggregationDateTime = @PreviousIntervalStartDateTime)
                )
      BEGIN
        -- we have non-dirty previous aggregation
        -- use its info to get all non-green monitors
        SET @UseAggregatesToBuildStartOfIntervalStateInd = 1
        
        SELECT @AggregationCoverViewName = 'v' + BaseTableName
        FROM StandardDatasetAggregationStorage
        WHERE (DatasetId = @DatasetId)
          AND (AggregationTypeId = @AggregationTypeId)
          AND (DependentTableInd = 0)
      END
      ELSE
      BEGIN
        -- pick up the latest state change
        -- for all me/monitor pairs when
        -- there was a change during the period
        -- or the last state wasn't green
        SET @UseAggregatesToBuildStartOfIntervalStateInd = 0
      END

    INSERT #StateEvent (ManagedEntityMonitorRowId, [DateTime], OldHealthState, NewHealthState)
    EXEC StateChangeEventList 
        @ManagedEntityMonitorRowId = NULL
       ,@IntervalStartDateTime = @StartDate
       ,@IntervalEndDateTime = @EndDate
       ,@UseAggregatesToBuildStartOfIntervalStateInd = @UseAggregatesToBuildStartOfIntervalStateInd
       ,@AggregationCoverViewName = @AggregationCoverViewName
       ,@AggregationDateTime = @PreviousIntervalStartDateTime

    SET @ResultXML = (
        SELECT mg.MGId as '@Id'
            ,(SELECT  sla.SLAId as '@Id'
                ,(SELECT slame.SLAMEId as '@Id',
                    (SELECT slo.SLOId as '@Id',
                        (SELECT slome.slomeId as '@Id', me.DisplayName as '@Name', ISNULL(met_ds.[Name], met.ManagedEntityTypeDefaultName) as '@Type', ISNULL(me.Path,'') as '@Path'
                            ,(
                                SELECT s.DateTime as '@DateTime',
                                       CASE newhealthstate
                                            WHEN 0 THEN ~mslompv.DisabledStateUndesiredInd
                                            WHEN 1 THEN 1
                                            WHEN 2 THEN ~mslompv.YellowStateUndesiredInd
                                            WHEN 3 THEN 0
                                            WHEN 100 THEN ~mslompv.UnPlannedMaintenanceStateUndesiredInd
                                            WHEN 101 THEN ~mslompv.PlannedMaintenanceStateUndesiredInd
                                            WHEN 50  THEN ~mslompv.HealthServiceUnavailableStateUndesiredInd
                                            ELSE 1
                                            END as '@NHS',
                                        CASE s.DateTime
                                            WHEN @StartDate THEN 1
                                            ELSE
                                                CASE oldhealthstate
                                                    WHEN 0 THEN ~mslompv.DisabledStateUndesiredInd
                                                    WHEN 1 THEN 1
                                                    WHEN 2 THEN ~mslompv.YellowStateUndesiredInd
                                                    WHEN 3 THEN 0
                                                    WHEN 100 THEN ~mslompv.UnPlannedMaintenanceStateUndesiredInd
                                                    WHEN 101 THEN ~mslompv.PlannedMaintenanceStateUndesiredInd
                                                    WHEN 50    THEN ~mslompv.HealthServiceUnavailableStateUndesiredInd
                                                    ELSE 1
                                                END 
                                        END as '@OHS'
                                FROM #SLDetails sldetails
                                JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = sldetails.SLOId)
                                JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = sldetails.MGId)
                                JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = mslompv.ServiceLevelObjectiveManagementPackVersionRowId)
                                JOIN vManagedEntityMonitor mem ON (sldetails.SLOMEId = mem.ManagedEntityRowId) AND (mem.MonitorRowId = mslompv.MonitorRowId)
                                JOIN #StateEvent s ON (mem.ManagedEntityMonitorRowId = s.ManagedEntityMonitorRowId)
                                WHERE 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 = sldetails.MGId)
                                        AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
                                    ) AND
                                sldetails.MGId = mg.MGId AND
                                sldetails.SLAId = sla.SLAId AND
                                sldetails.SLAMEId = slame.SLAMEId AND
                                sldetails.SLOId = slo.SLOId AND
                                sldetails.SLOMEId = slome.SLOMEId
                                FOR XML PATH('SLEvent'), TYPE)
                         FROM  (SELECT DISTINCT MGId, SLAID, SLAMEID, SLOID, SLOMEID FROM #SLDetails) slome 
                                JOIN vManagedEntity me ON (slome.SLOMEId = me.ManagedEntityRowId)
                                JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
                                LEFT JOIN vDisplayString met_ds ON (met.ManagedEntityTypeGuid = met_ds.ElementGuid) AND (met_ds.LanguageCode = @LanguageCode)
                         WHERE slome.MGId = mg.MGId AND
                                slome.SLAId = sla.SLAId AND
                                slome.SLAMEId = slame.SLAMEId AND
                                slome.SLOId = slo.SLOId
                         FOR XML PATH('SLOME'), TYPE)
                     FROM  (SELECT DISTINCT MGId, SLAID, SLAMEID, SLOId FROM #SLDetails) slo 
                     WHERE slo.MGId = mg.MGId AND
                          slo.SLAId = sla.SLAId AND
                          slo.SLAMEId = slame.SLAMEId
                     FOR XML PATH('SLO'), TYPE)
                FROM  (SELECT DISTINCT MGId, SLAID, SLAMEId FROM #SLDetails) slame
                WHERE slame.MGId = mg.MGId AND
                      slame.SLAId = sla.SLAId 
                FOR XML PATH('SLAME'), TYPE)
            FROM (SELECT DISTINCT MGId, SLAID FROM #SLDetails) sla 
            WHERE sla.mgId = mg.mgId
            FOR XML PATH('SLA'), TYPE)
        FROM (SELECT DISTINCT mgId FROM #SLDetails) mg
        FOR XML PATH('MG'), ROOT('METRICS'), TYPE
    )
    SELECT @ResultXML as Result
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetStateInterval]
    @IntervalStartDate    DateTime
   ,@IntervalEndDate        DateTime
AS
BEGIN

    IF OBJECT_ID('tempdb..#StateInterval') IS NOT NULL
    BEGIN
        DROP TABLE #StateInterval
    END
    -- Temporary table to hold the Time Interval and their state values.
    CREATE TABLE #StateInterval (
             ManagedEntityMonitorRowId INT
            ,IntervalStartDateTime  DATETIME
            ,IntervalEndDateTime    DATETIME
            ,HealthState            TINYINT
          )
    -- Populate #StateInterval from #StateEvent table. (Calling SP should create #StateEvent table.)
    INSERT #StateInterval (
             ManagedEntityMonitorRowId
            ,IntervalStartDateTime
            ,IntervalEndDateTime
            ,HealthState
    )
    SELECT * FROM 
    (
        SELECT
            l.ManagedEntityMonitorRowId
            ,CASE WHEN l.[DateTime] < @IntervalStartDate
                   THEN @IntervalStartDate
                   ELSE l.[DateTime]
             END AS IntervalStartDateTime
            ,CASE WHEN ISNULL(r.[DateTime], '99991231') > @IntervalEndDate
                   THEN @IntervalEndDate
                   ELSE r.[DateTime]
             END AS IntervalEndDateTime
            ,CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
                   THEN l.NewHealthState
                   ELSE ISNULL(r.OldHealthState, l.NewHealthState)
            END AS HealthState
            FROM #StateEvent l
            LEFT JOIN #StateEvent r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) 
                    AND (l.[DateTime] < r.[DateTime])
            WHERE (NOT EXISTS (SELECT * FROM #StateEvent m
                                 WHERE (l.ManagedEntityMonitorRowId = m.ManagedEntityMonitorRowId)
                                   AND (m.[DateTime] > l.[DateTime])
                                   AND (m.[DateTime] < ISNULL(r.[DateTime], '99991231'))
                                )
                    )
                AND (l.[DateTime] < @IntervalEndDate)
    ) StateInterval
    WHERE (StateInterval.IntervalStartDateTime < StateInterval.IntervalEndDateTime)

    -- Populate the RAW Availability data
    INSERT INTO #TEMPRAW(
                        [DateTime]
                        ,[IntervalDurationMilliseconds] 
                        ,ManagedEntityMonitorRowId
                        ,InRedStateMilliseconds
                        ,InYellowStateMilliseconds
                        ,InDisabledStateMilliseconds
                        ,InPlannedMaintenanceMilliseconds
                        ,InUnplannedMaintenanceMilliseconds
                        ,HealthServiceUnavailableMilliseconds
                        ,IntervalEndHealthState
                        )
    SELECT  @IntervalStartDate
            ,CAST(ABS(datediff(ms,@IntervalEndDate , @IntervalStartDate)) as NVARCHAR)
            ,i1.ManagedEntityMonitorRowId
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 3 THEN 1 ELSE 0 END) )
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 2 THEN 1 ELSE 0 END) )
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 0 THEN 1 ELSE 0 END) )
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 101 THEN 1 ELSE 0 END) )
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 100 THEN 1 ELSE 0 END) )
            ,SUM(ABS(DATEDIFF(ms, i1.IntervalEndDateTime, i1.IntervalStartDateTime)) * (CASE i1.HealthState WHEN 50 THEN 1 ELSE 0 END) )
            ,MAX(i2.HealthState) *
                CASE WHEN MAX(i2.HealthState) IN (1, 50) THEN 1
                     WHEN EXISTS (SELECT * FROM vManagedEntityMonitor mem
                                  JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
                                  JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem.MonitorRowId)
                                  JOIN vManagementGroupManagementPackVersion mgmpv ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND
                                                                                         (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND
                                                                                         (@IntervalEndDate >= mgmpv.InstalledDateTime) AND 
                                                                                         (@IntervalEndDate < ISNULL(mgmpv.DeletedDateTime, '99991231')))
                                  JOIN vManagedEntityManagementGroup memg ON ((mem.ManagedEntityRowId = memg.ManagedEntityRowId) AND
                                                                                (@IntervalEndDate >= memg.FromDateTime) AND 
                                                                                (@IntervalEndDate < ISNULL(memg.ToDateTime, '99991231') ))
                                  WHERE (mem.ManagedEntityMonitorRowId = i1.ManagedEntityMonitorRowId)
                                )
                    THEN 1
                    ELSE NULL
                END
    FROM #StateInterval i1
    JOIN #StateInterval i2 ON (i1.ManagedEntityMonitorRowId = i2.ManagedEntityMonitorRowId)
    WHERE (i2.IntervalEndDateTime = @IntervalEndDate) AND (i1.HealthState <> 1)
    GROUP BY i1.ManagedEntityMonitorRowId

    -- Populate the RAW Performance data
    INSERT #TempPerfRaw (
                        [DateTime]
                        ,PerformanceRuleInstanceRowId
                        ,ManagedEntityRowId
                        ,SampleCount
                        ,AverageValue
                        ,MinValue
                        ,MaxValue
                        ,StandardDeviation
                        )
    SELECT
        @IntervalStartDate
        ,PerformanceRuleInstanceRowId
        ,ManagedEntityRowId
        ,COUNT(*)
        ,AVG(SampleValue)
        ,MIN(SampleValue)
        ,MAX(SampleValue)
        ,ISNULL(STDEV(SampleValue), 0)
    FROM [Perf].[vPerfRaw]
    WHERE ([DateTime] >= @IntervalStartDate)
         AND ([DateTime] < @IntervalEndDate)
    GROUP BY PerformanceRuleInstanceRowId, ManagedEntityRowId
      
END
GO   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet_60Minutes]
     @StartDate                            DATETIME        -- Sample 2008-12-16 18:59:00.000
    ,@EndDate                            DATETIME        -- Sample 2008-12-16 18:59:00.000
    ,@ServiceLevelAgreementXml            xml
    ,@AggregationTypeId                    int
AS
BEGIN
-- Exec [Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet_60Minutes] '2009-01-19 10:10:10.123', '2009-01-19 11:10:10.123', '3:1', 'ENU'
-- This stored procedure will return all metrics data for the specified AgreementRowId and ManagementGroupRowId combination.
    
    DECLARE 
    @ContainmentRelationshipTypeRowId    INT
    ,@Level                                INT
    ,@RowCount                            INT
    ,@Statement                            NVARCHAR(MAX)
    ,@Verbose                            INT
    ,@ErrorInd                            BIT
    ,@ErrorMessage                        NVARCHAR(MAX)
    ,@ErrorNumber                        INT
    ,@ErrorSeverity                        INT
    ,@ErrorState                        INT
    ,@ErrorLine                            INT
    ,@ErrorProcedure                    NVARCHAR(256)
    ,@ErrorMessageText                    NVARCHAR(MAX)
    ,@IntervalStartTime                    DATETIME
    ,@IntervalEndTime                    DATETIME

    -- Verbose = 0 (For normal usage) / 1 (shd be set only to debug the Stored procedure)
    SET @Verbose = 0

    BEGIN TRY
        -- When Aggregation Type = 'RAW'
        --    Remove Seconds and Milliseconds - This is required as the vDateTime Dimension's lowest detail is minutes.
        --    Reject Business hour selections.
        SET @StartDate = CONVERT(SMALLDATETIME, @StartDate)
        SET @EndDate = CONVERT(SMALLDATETIME, @EndDate)
        
        -- build a list of all entities covered by Sla's
        IF OBJECT_ID('tempdb..#ServiceLevelAgreementManagedEntity') IS NOT NULL
        BEGIN
            DROP TABLE #ServiceLevelAgreementManagedEntity
        END

        CREATE TABLE #ServiceLevelAgreementManagedEntity (
           ServiceLevelAgreementRowId     int     NOT NULL
          ,ManagementGroupRowId           int     NOT NULL
          ,TargetManagedEntityTypeRowId   int     NULL
          ,ContextManagedEntityTypeRowId  int     NULL
          ,ContextManagedEntityGuid       uniqueidentifier  NULL
          ,ManagedEntityRowId             int     NOT NULL
        )

        INSERT #ServiceLevelAgreementManagedEntity
        EXEC ServiceLevelAgreementManagedEntityGet
              @StartDate
             ,@EndDate
             ,@ServiceLevelAgreementXml

        IF (@Verbose = 1)
        BEGIN
            SELECT '#ServiceLevelAgreementManagedEntity'
            SELECT * FROM #ServiceLevelAgreementManagedEntity
        END

        -- build list of Slo's covered by given Sla's
        IF OBJECT_ID('tempdb..#ServiceLevelObjective') IS NOT NULL
        BEGIN
            DROP TABLE #ServiceLevelObjective
        END

        CREATE TABLE #ServiceLevelObjective (
           ServiceLevelAgreementRowId     int         NOT NULL
          ,ManagementGroupRowId           int         NOT NULL
          ,ServiceLevelObjectiveRowId     int         NOT NULL
          ,TargetManagedEntityTypeRowId   int         NOT NULL
          ,Goal                           float       NOT NULL
          ,DesiredObjective               varchar(5)  NOT NULL
          ,AggregationMethod              char(3)     NOT NULL      
        )
        
        INSERT #ServiceLevelObjective
        EXEC ServiceLevelObjectiveGetByServiceLevelAgreement
              @StartDate
             ,@EndDate
             ,@ServiceLevelAgreementXml

        IF (@Verbose = 1)
        BEGIN
            SELECT '#ServiceLevelObjective'
            SELECT * FROM #ServiceLevelObjective
        END

        -- build a list of all containment relationships
        IF OBJECT_ID('tempdb..#ContainmentRelationshipType') IS NOT NULL
        BEGIN
            DROP TABLE #ContainmentRelationshipType
        END
        CREATE TABLE #ContainmentRelationshipType (
           RelationshipTypeRowId  int PRIMARY KEY
        )
        
        SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId
        FROM RelationshipType
        WHERE RelationshipTypeSystemName = 'System.Containment'
        
        IF (@ContainmentRelationshipTypeRowId IS NOT NULL)
        BEGIN
          INSERT #ContainmentRelationshipType (RelationshipTypeRowId)
          SELECT RelationshipTypeRowId
          FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
        END
        
        -- walk down the containment relationship for Sla entities
        IF OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL
        BEGIN
            DROP TABLE #ContainedManagedEntity
        END

        CREATE TABLE #ContainedManagedEntity (
           ServiceLevelAgreementRowId     int     NOT NULL
          ,ManagementGroupRowId           int     NOT NULL
          ,ServiceLevelAgreementManagedEntityRowId int NOT NULL
          ,ManagedEntityRowId             int     NOT NULL
          ,[Level]                        int     NOT NULL
        )
        
        INSERT #ContainedManagedEntity (
             ServiceLevelAgreementRowId
            ,ManagementGroupRowId
            ,ServiceLevelAgreementManagedEntityRowId
            ,ManagedEntityRowId
            ,[Level]
        )
        SELECT
           sla.ServiceLevelAgreementRowId
          ,sla.ManagementGroupRowId
          ,sla.ManagedEntityRowId
          ,sla.ManagedEntityRowId
          ,0
        FROM #ServiceLevelAgreementManagedEntity sla

        SET @Level = 1
        SET @RowCount = 1
        
        WHILE (@RowCount > 0)
        BEGIN
          INSERT #ContainedManagedEntity (
             ServiceLevelAgreementRowId
            ,ManagementGroupRowId
            ,ServiceLevelAgreementManagedEntityRowId
            ,ManagedEntityRowId
            ,[Level]
          )
          SELECT
             source_me.ServiceLevelAgreementRowId
            ,source_me.ManagementGroupRowId
            ,source_me.ServiceLevelAgreementManagedEntityRowId
            ,r.TargetManagedEntityRowId
            ,@Level
          FROM #ContainedManagedEntity source_me
                JOIN vRelationship r ON (source_me.ManagedEntityRowId = r.SourceManagedEntityRowId)
                JOIN #ContainmentRelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
          WHERE (source_me.Level = @Level - 1)
            AND EXISTS (SELECT * 
                        FROM vRelationshipManagementGroup rmg
                        WHERE (rmg.RelationshipRowId = r.RelationshipRowId)
                          AND (rmg.FromDateTime < ISNULL(@EndDate, '99991231'))
                          AND (ISNULL(rmg.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
                       )
          
          SET @RowCount = @@ROWCOUNT
          SET @Level = @Level + 1
        END
        
        -- build a list of Slo entities
        IF OBJECT_ID('tempdb..#ServiceLevelObjectiveManagedEntity') IS NOT NULL
        BEGIN
            DROP TABLE #ServiceLevelObjectiveManagedEntity
        END
        CREATE TABLE #ServiceLevelObjectiveManagedEntity (
           ServiceLevelObjectiveRowId               int NOT NULL
          ,ManagementGroupRowId                     int NOT NULL
          ,ServiceLevelObjectiveManagedEntityRowId  int NOT NULL
        )
        
        INSERT #ServiceLevelObjectiveManagedEntity (
           ServiceLevelObjectiveRowId
          ,ManagementGroupRowId
          ,ServiceLevelObjectiveManagedEntityRowId
        )
        SELECT DISTINCT
           slo.ServiceLevelObjectiveRowId
          ,slo.ManagementGroupRowId
          ,me.ManagedEntityRowId
        FROM #ServiceLevelObjective slo
              JOIN #ContainedManagedEntity me ON (slo.ManagementGroupRowId = me.ManagementGroupRowId) AND (slo.ServiceLevelAgreementRowId = me.ServiceLevelAgreementRowId)
              CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(slo.TargetManagedEntityTypeRowId, 0) as t
        WHERE EXISTS (SELECT *
                      FROM vTypedManagedEntity tme 
                      WHERE (t.ManagedEntityTypeRowId = tme.ManagedEntityTypeRowId)
                        AND (me.ManagedEntityRowId = tme.ManagedEntityRowId)
                        AND (tme.FromDateTime < ISNULL(@EndDate, '99991231'))
                        AND (ISNULL(tme.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
                     )
          
        IF (@Verbose = 1)
        BEGIN
            SELECT '#ServiceLevelObjectiveManagedEntity'
            SELECT * from #ServiceLevelObjectiveManagedEntity
        END

        IF OBJECT_ID('tempdb..#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL
        BEGIN
            DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
        END
        CREATE TABLE #ServiceLevelObjectiveManagedEntityDetail (
             ServiceLevelObjectiveRowId       int       NOT NULL
            ,ManagementGroupRowId             int       NOT NULL
            ,ServiceLevelObjectiveManagedEntityRowId int NOT NULL
            ,[DateTime]                       datetime  NOT NULL
            ,ActualValue                      float     NOT NULL
            ,Weight                           int       NOT NULL
            ,TotalDowntimeMilliseconds          int       NULL
        )

        CREATE TABLE #ManagedEntityMonitor
        (
            ManagedEntityMonitorRowId INT
        )

        -- Populate on the required ManagedEntityMonitors before making a call to StateChangeEventList.
        INSERT INTO #ManagedEntityMonitor(ManagedEntityMonitorRowId)
        SELECT distinct
                    mem.ManagedEntityMonitorRowId
        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 vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = mslompv.ServiceLevelObjectiveManagementPackVersionRowId)
                JOIN vManagedEntityMonitor mem ON (slome.ServiceLevelObjectiveManagedEntityRowId = mem.ManagedEntityRowId) AND (mem.MonitorRowId = mslompv.MonitorRowId)

        -- State Event table for storing the health state changes for all ManagedEntityMonitorRowId.
          IF OBJECT_ID('tempdb..#StateEvent') IS NOT NULL
        BEGIN
            DROP TABLE #StateEvent
        END
        CREATE TABLE #StateEvent (
         ManagedEntityMonitorRowId int
        ,[DateTime]           datetime
        ,OldHealthState       tinyint
        ,NewHealthState       tinyint
        )
         
        INSERT #StateEvent (ManagedEntityMonitorRowId, [DateTime], OldHealthState, NewHealthState)
        EXEC StateChangeEventList 
            @ManagedEntityMonitorRowId = NULL
           ,@IntervalStartDateTime = @StartDate
           ,@IntervalEndDateTime = @EndDate
           ,@UseAggregatesToBuildStartOfIntervalStateInd = 0
           ,@AggregationCoverViewName = NULL
           ,@AggregationDateTime = NULL
        
        IF (@Verbose = 1)
        BEGIN
            SELECT '#StateEvent'
            SELECT * FROM #StateEvent order by managedentitymonitorrowid
        END
        
        -- Calculations on RAW DATA section.

        -- Temp Raw table for 'Availability' details.
        IF OBJECT_ID('tempdb..#tempRaw') IS NOT NULL
        BEGIN
            DROP TABLE #tempRaw
        END

        CREATE TABLE #tempRaw
        (
             ManagedEntityMonitorRowId                INT
            ,[DateTime]                                DATETIME
            ,[IntervalDurationMilliseconds]            INT DEFAULT 0
            ,[ManagedEntityMonitorExistedInd]        INT DEFAULT 0
            ,InRedStateMilliseconds                    INT
            ,InYellowStateMilliseconds                INT
            ,InDisabledStateMilliseconds            INT
            ,InPlannedMaintenanceMilliseconds        INT
            ,InUnplannedMaintenanceMilliseconds        INT
            ,HealthServiceUnavailableMilliseconds    INT
            ,IntervalEndHealthState                    TINYINT
            ,InWhiteStateMilliSeconds                INT DEFAULT 0
        )
        -- Temp Raw table for 'Performance' details.
        IF OBJECT_ID('tempdb..#TempPerfRaw') IS NOT NULL
        BEGIN
            DROP TABLE #TempPerfRaw
        END
        CREATE TABLE #TempPerfRaw
        (
            [DateTime]                        DATETIME,
            [PerformanceRuleInstanceRowId]    INT,
            [ManagedEntityRowId]            INT,
            [SampleCount]                    INT,
            [AverageValue]                    FLOAT,
            [MinValue]                        FLOAT,
            [MaxValue]                        FLOAT,
            [StandardDeviation]                FLOAT
        )
        
        -- For the Last 60 minutes - we need to aggregate data for 10 minute time intervals.
        SET @IntervalStartTime = @StartDate
        WHILE (@IntervalStartTime < @EndDate)
        BEGIN
            SET @IntervalEndTime = DATEADD(mi, 10, @IntervalStartTime)
            -- Populate the aggregated Availability and Performance data for the specified interval.
            EXEC GetStateInterval @IntervalStartTime, @IntervalEndTime
            SET @IntervalStartTime = @IntervalEndTime
        END

        IF (@Verbose = 1)
        BEGIN
            SELECT '#tempRaw'
            SELECT * from #TempRaw order by managedentitymonitorrowid
            
            SELECT 'TempPerfRaw'
            SELECT * FROM #TempPerfRaw 
        END

        INSERT #ServiceLevelObjectiveManagedEntityDetail (
                 ServiceLevelObjectiveRowId
                ,ManagementGroupRowId
                ,ServiceLevelObjectiveManagedEntityRowId
                ,[DateTime]
                ,ActualValue
                ,Weight
                 ,TotalDowntimeMilliseconds                      
            )
        SELECT
            slome.ServiceLevelObjectiveRowId
            ,slome.ManagementGroupRowId
            ,slome.ServiceLevelObjectiveManagedEntityRowId
            ,s.DateTime
            ,ActualValue = 100.0 * (
                           s.IntervalDurationMilliseconds 
                         - s.InRedStateMilliseconds
                         - s.InYellowStateMilliseconds * mslompv.YellowStateUndesiredInd
                         - s.InPlannedMaintenanceMilliseconds * mslompv.PlannedMaintenanceStateUndesiredInd
                         - s.InUnplannedMaintenanceMilliseconds * mslompv.UnplannedMaintenanceStateUndesiredInd
                         - s.InDisabledStateMilliseconds * mslompv.DisabledStateUndesiredInd
                         - s.HealthServiceUnavailableMilliseconds * mslompv.HealthServiceUnavailableStateUndesiredInd
                         - s.InWhiteStateMilliseconds * mslompv.UnmonitoredStateUndersiredInd
                        ) / s.IntervalDurationMilliseconds
            ,Weight = 1 
            ,(s.InRedStateMilliseconds +  s.InYellowStateMilliseconds * mslompv.YellowStateUndesiredInd +  s.InDisabledStateMilliseconds * mslompv.DisabledStateUndesiredInd +  s.InPlannedMaintenanceMilliseconds * mslompv.PlannedMaintenanceStateUndesiredInd+  s.InUnplannedMaintenanceMilliseconds * mslompv.UnplannedMaintenanceStateUndesiredInd+  s.HealthServiceUnavailableMilliseconds * mslompv.HealthServiceUnavailableStateUndesiredInd + s.InWhiteStateMilliseconds * mslompv.UnmonitoredStateUndersiredInd ) 
        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 vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = mslompv.ServiceLevelObjectiveManagementPackVersionRowId)
             JOIN vManagedEntityMonitor mem ON (slome.ServiceLevelObjectiveManagedEntityRowId = mem.ManagedEntityRowId) AND (mem.MonitorRowId = mslompv.MonitorRowId)
             JOIN (
                    SELECT  
                        [DateTime],
                        [Date],
                        [Time],
                        ManagedEntityMonitorRowId,
                        ManagedEntityRowId,
                        MonitorRowId,
                        IntervalDurationMilliseconds,
                        InYellowStateMilliseconds,
                        InRedStateMilliseconds,
                        InPlannedMaintenanceMilliseconds,
                        InUnplannedMaintenanceMilliseconds,
                        InDisabledStateMilliseconds,
                        HealthServiceUnavailableMilliseconds,
                        InWhiteStateMilliseconds = CASE WHEN ((ManagedEntityMonitorExistedInd = 0) OR                      
                                                        (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE()))              
                                                        THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds        
                                                        ELSE 0      
                                                    END,
                        InGreenStateMilliseconds = CASE WHEN ((ManagedEntityMonitorExistedInd = 1) AND
                                                        (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))
                                                        THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds        
                                                        ELSE 0
                                                    END  
                    FROM (
                            SELECT       
                                dt.[DateTime],
                                dt.[Date],
                                dt.[Time],
                                ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId,
                                ManagedEntityRowId = mem.ManagedEntityRowId,
                                MonitorRowId = mem.MonitorRowId,
                                ManagedEntityMonitorExistedInd = CASE WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1 END,
                                IntervalDurationMilliseconds = 10 * 60 * 1000,
                                InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0),
                                InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0),
                                InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0),
                                InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0),
                                InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0),
                                HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)    
                            FROM vDateTime dt
                            CROSS JOIN vManagedEntityMonitor mem          
                            LEFT JOIN #TempRaw s ON ((s.[DateTime] = dt.[DateTime]) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))          
                            LEFT JOIN (SELECT DISTINCT     
                                            mem2.ManagedEntityMonitorRowId, 
                                            dt2.[Date], 
                                            dt2.[Time]
                                        FROM vDateTime dt2 
                                        CROSS JOIN vManagedEntityMonitor mem2 
                                        JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId) 
                                        JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem2.MonitorRowId) 
                                        JOIN vManagementGroupManagementPackVersion mgmpv ON 
                                                ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) 
                                                AND (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) 
                                                AND (dt2.[DateTime] >= mgmpv.InstalledDateTime) 
                                                AND  (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, '99991231'))) 
                                        JOIN vManagedEntityManagementGroup memg ON 
                                                ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) 
                                                AND (dt2.[DateTime] >= memg.FromDateTime) 
                                                AND  (dt2.[DateTime] < ISNULL(memg.ToDateTime, '99991231'))) 
                                        WHERE (dt2.Minute in (DatePart(mi,@StartDate), (DatePart(mi,@StartDate) + 10) %60,(DatePart(mi,@StartDate) + 20) %60,(DatePart(mi,@StartDate) + 30) %60, (DatePart(mi,@StartDate) + 40) %60, (DatePart(mi,@StartDate) + 50) %60 )))AS mem_exists 
                                ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) 
                                    AND (mem_exists.[Date] = dt.[Date]) 
                                    AND (mem_exists.[Time] = dt.[Time]) 
                            WHERE (dt.Minute in (DatePart(mi,@StartDate), (DatePart(mi,@StartDate) + 10) %60, (DatePart(mi,@StartDate) + 20) %60, (DatePart(mi,@StartDate) + 30) %60, (DatePart(mi,@StartDate) + 40) %60, (DatePart(mi,@StartDate) + 50) %60   ))) AS tbl  ) s
                ON (mem.ManagedEntityMonitorRowId = s.ManagedEntityMonitorRowId)
                WHERE    (s.[DateTime] >= @StartDate )
                        AND (s.[DateTime] <  @EndDate )
                        AND (s.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @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))
                                    
        UNION ALL

        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 #tempPerfRaw 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))
        -- End of calculation of RAW data.                

        IF (@Verbose = 1)
        BEGIN
            SELECT '#ServiceLevelObjectiveManagedEntityDetail'
            SELECT * FROM #ServiceLevelObjectiveManagedEntityDetail
        END

        SELECT DISTINCT
           slame.ServiceLevelAgreementRowId
          ,slame.ManagementGroupRowId
           ,ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId
          ,slomed.ServiceLevelObjectiveRowId
          ,slomed.ServiceLevelObjectiveManagedEntityRowId
          ,slomed.[DateTime]
          ,slomed.ActualValue
          ,slomed.Weight
          ,slo.Goal
          ,slo.DesiredObjective
          ,slo.AggregationMethod
          ,slomed.TotalDowntimeMilliseconds
          FROM #ServiceLevelObjectiveManagedEntityDetail slomed
                JOIN #ServiceLevelObjective slo ON (slo.ServiceLevelObjectiveRowId = slomed.ServiceLevelObjectiveRowId) AND (slo.ManagementGroupRowId = slomed.ManagementGroupRowId)
                JOIN #ContainedManagedEntity cme ON (slomed.ServiceLevelObjectiveManagedEntityRowId = cme.ManagedEntityRowId)
                JOIN #ServiceLevelAgreementManagedEntity slame ON (slo.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND (slame.ManagementGroupRowId = slo.ManagementGroupRowId) AND (cme.ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId)

    
    END TRY
    BEGIN CATCH
        SELECT 
            @ErrorNumber = ERROR_NUMBER()
            ,@ErrorSeverity = ERROR_SEVERITY()
            ,@ErrorState = ERROR_STATE()      ,@ErrorLine = ERROR_LINE()
            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
            ,@ErrorMessageText = ERROR_MESSAGE()
        SET @ErrorInd = 1
    END CATCH

Cleanup:
    -- report error if any
    IF (@ErrorInd = 1)
    BEGIN
        DECLARE @AdjustedErrorSeverity int
        SET @AdjustedErrorSeverity = CASE WHEN @ErrorSeverity > 18 THEN 18 ELSE @ErrorSeverity END
    
    RAISERROR (777971002, @AdjustedErrorSeverity, 1
      ,@ErrorNumber
      ,@ErrorSeverity
      ,@ErrorState
      ,@ErrorProcedure
      ,@ErrorLine
      ,@ErrorMessageText
    )
  END

END
GO


-- CREATE Role and Provide access to stored procedures / Functions
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'SLDReader' AND TYPE = 'R')
BEGIN
    CREATE ROLE SLDReader
END
GRANT EXECUTE ON [dbo].[GetStateInterval] TO [SldReader]
GRANT EXECUTE ON [dbo].[GetSLADetails] TO [SldReader]
GRANT EXECUTE ON [dbo].[GetServiceLevelFailures] TO [SldReader]
GRANT EXECUTE ON [dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet_60Minutes] TO [SldReader]
GRANT EXECUTE ON [dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet] TO [SldReader]
GRANT EXECUTE ON [dbo].[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet] TO [SldReader]

GRANT SELECT ON [perf].[vPerfRaw] TO [SLDREADER]
GRANT SELECT ON [perf].[vPerfHourly] TO [SLDREADER]
GRANT SELECT ON [perf].[vPerfDaily] TO [SLDREADER]
GRANT SELECT ON [state].[vStateRaw] TO [SLDREADER]
GRANT SELECT ON [state].[vStateHourly] TO [SLDREADER]
GRANT SELECT ON [state].[vStateDaily] TO [SLDREADER]
GRANT SELECT ON [dbo].[vStateFull] TO [SLDREADER]
GRANT SELECT ON [dbo].[vStateHourlyFull] TO [SLDREADER]
GRANT SELECT ON [dbo].[vStateDailyFull] TO [SLDREADER]

GRANT SELECT ON [dbo].[vHealthServiceOutage] TO [SLDREADER]
GRANT SELECT ON [dbo].[vRelationship] TO [SLDREADER]
GRANT SELECT ON [dbo].[vRelationshipManagementGroup] TO [SLDREADER]
GRANT SELECT ON [dbo].[vManagedEntity] TO [SLDREADER]
GRANT SELECT ON [dbo].[vManagedEntityMonitor] TO [SLDREADER]
GRANT SELECT ON [dbo].[vMaintenanceMode] TO [SLDREADER]
GRANT SELECT ON [dbo].[vServiceLevelObjectiveManagementPackVersion] TO [SLDREADER]
GRANT SELECT ON [dbo].[vManagementGroupManagementPackVersion] TO [SLDREADER]
GRANT SELECT ON [dbo].[vMonitorServiceLevelObjectiveManagementPackVersion] TO [SLDREADER]
GRANT SELECT ON [dbo].[vPerformanceServiceLevelObjectiveManagementPackVersion] TO [SLDREADER]
GRANT SELECT ON [dbo].[vPerformanceRuleInstance] TO [SLDREADER]
GRANT SELECT ON [dbo].[StateHealthServiceOutage] TO [SLDREADER]
GRANT SELECT ON [dbo].[StateInvalidatedHealthServiceOutage] TO [SLDREADER]

GO