image

/*Number of state change events per day (trending)
Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/)
04-08-2011
*/
 
DECLARE @RowCount AS INT,
    @MGID AS INT,
    @TimeZoneOffset AS INT,
    @OffSetDays AS INT,
    @StartDate AS DATE,
    @EndDate AS DATE
    
SET @RowCount = 20
SET @MGID = 1
SET @TimeZoneOffset = 5
SET @OffSetDays = 60
SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE()))
SET @EndDate = DATEADD(hour, @TimeZoneOffset, GETDATE())
 
SELECT CONVERT(VARCHAR(10), vSR.DateTime, 101) AS 'Date',
        COUNT(*) AS 'Count'
FROM  State.vStateRaw AS vSR INNER JOIN
        vManagedEntityMonitor AS vMEM ON vMEM.ManagedEntityMonitorRowId = vSR.ManagedEntityMonitorRowId INNER JOIN
        vManagedEntity AS VME ON VME.ManagedEntityRowId = vMEM.ManagedEntityRowId INNER JOIN
        vManagementGroup AS vMG ON vMG.ManagementGroupRowId = VME.ManagementGroupRowId
WHERE (vSR.DateTime BETWEEN @StartDate AND @EndDate) AND (vMG.ManagementGroupRowId = @MGID)
GROUP BY CONVERT(VARCHAR(10), vSR.DateTime, 101)
ORDER BY 'Date' DESC

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.