Report writing help.

adamras

Hey all,

I was wondering if I could turn to the community for some help writing a report.  My SQL skills are very limited so I am struggling with the complexity of this.  Though I don't think it's TOO complex.

I would like to have a report that would summarize the uptime of the devices in a particular departments by day of the month.  For example Feb 02/2010 - 99.87%.  Most likely it would be using in a "last month" context.  Now, the problem that I am seeing is that because of the "Hourly Statistics Retention" that anything newer than 30 days is showing a percentage for every hour of the day.  So to combat that I chose a filter that filters "Hour of Day (24 Hour format) is equal to 0".  So I run this and it remedies the issues with the 30 days, but I still have an issue with the "7 Day Detailed Statistics Retention".  I was unable to find a filter that would let me get rid of that.

The current SQL query looks like:

SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
ResponseTime.DateTime AS Date

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


WHERE
( DateTime BETWEEN 40208 AND 40231 )
 AND 
(
  (Nodes.Department = 'Telecommunications') AND
  (DatePart(Hour,DateTime) = 0)
)


GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
ResponseTime.DateTime


ORDER BY SummaryDate ASC, 3 ASC


Thank you so much in advance.

 

--adam

scottd

Adam, Try this instead...

I have also included some time variables i like to use that will make it eaiser to select the first of the month or the first of the year so you can modify the query as needed. the query as it stands will list 2 columns; Month and Average Availability for example, March 2010 | 100.00%. Or you could run it with the @YearBegin variable and you would get something like;

January 2010 | 99.55%

February 2010 |100.00%

March 2010 |98.62%

You do not have to use the time variables, but you can see in the 'where' part how it comes in handy.

Anyway, hope this helps...

--Setup time variables
DECLARE @today DATETIME
DECLARE @MonthBegin DATETIME
DECLARE @yearBegin DATETIME
DECLARE @month INTEGER
DECLARE @day INTEGER
DECLARE @hour INTEGER
DECLARE @minute INTEGER
DECLARE @second INTEGER
SET @today = GetDate()
SET @MonthBegin = @today
SET @yearBegin = @today
SET @month = DatePart(mm, @today)
SET @day = DatePart(dd, @today)
SET @hour = DatePart(hh, @today)
SET @minute = DatePart(mi, @today)
SET @second = DatePart(ss, @today)
SET @MonthBegin = DateAdd(dd, -@day  + 1, @MonthBegin)
SET @MonthBegin = DateAdd(hh, -@hour, @MonthBegin)
SET @MonthBegin = DateAdd(mi, -@minute, @MonthBegin)
SET @MonthBegin = DateAdd(ss, -@second, @MonthBegin)
SET @yearBegin = DateAdd(mm, -@month + 1, @yearBegin)
SET @yearBegin = DateAdd(dd, -@day  + 1, @yearBegin)
SET @yearBegin = DateAdd(hh, -@hour, @yearBegin)
SET @yearBegin = DateAdd(mi, -@minute, @yearBegin)
SET @yearBegin = DateAdd(ss, -@second, @yearBegin)

SELECT  TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN @MonthBegin AND getdate() )
 AND 
(
  (Nodes.department = 'Telecommunications')
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Application

ORDER BY SummaryMonth ASC