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

    SQL summary report groups - Percentage calculation

    Wal

      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