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)
--=======================================================================
insert #t_out (Comments,OutageDurationInMinutes)
--===================fill in (begin)===================
SELECT
Nodes.Comments,
------------------------------Add here another column-------------------
--address,
------------------------------------------------------------------------
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
---------------------------------------Add here another filter---------------------
-- and caption like '%pix%'
-------------------------------------------------------------------------------------
ORDER BY caption asc
--===================fill in (END)===================
select comments, sum(OutageDurationInMinutes) as OutageDurationInMinutes,
cast(floor (sum(OutageDurationInMinutes)/60) as varchar(3))+':'+cast(sum(OutageDurationInMinutes)-60*floor (sum(OutageDurationInMinutes)/60) as varchar(2)) as OutageDurationInHours
from #t_out
group by comments
DROP TABLE #t_out
