0 Replies Latest reply on Mar 15, 2012 11:12 PM by Wal

# SQL summary report groups - Percentage calculation

Hi, wondering if something can help me with percentage calculation in SQL.  Got this report from another thread within thwack & have modified so my devices are grouped using the nodes.comments table to report on group devices.  The report comes out in hours & minutes but would like it to show percentage down.

---!!!!!!!!!!!!!!!!!!!!!!!!!!

set dateformat dmy

---!!!!!!!!!!!!!!!!!!!!!!!!!!

declare @period smallint

declare @periodbegin datetime

declare @periodend datetime

declare @Sensitivity smallint

--Add here Sensitivity (min), period(days) or periodbegin/periodend==========

set @Sensitivity=25

set @period='7'

set @periodbegin=''

set @periodend=''

--======================================================================

if @period!=''

begin

set @periodbegin=(GetDate()-@period)

set @periodend=getdate()

end

if @periodend='' set @periodend=getdate()

--============================create temp table==========================

IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL

DROP TABLE #t_out

create table #t_out (Comments  Varchar(255), OutageDurationInMinutes int)

--=======================================================================

--===================fill in (begin)===================

SELECT

------------------------------------------------------------------------

DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime

WHERE

EndTime.EventTime > StartTime.EventTime AND

EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND

EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime))

AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON

StartTime.NetworkNode = Nodes.NodeID

WHERE

(StartTime.EventType = 1) AND

(StartTime.NetObjectType = 'N') AND (

---------------------------------period---------------------

EventTime > @periodbegin

AND EventTime < @periodend

-----------------------------------------------------------------------------------

)and (DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime

WHERE

EndTime.EventTime > StartTime.EventTime AND

EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND

EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)))

--------------------------------------Sensitivity (min)-----------------------

> @Sensitivity

--                                             and caption like '%pix%'

-------------------------------------------------------------------------------------

ORDER BY caption asc

--===================fill in (END)===================