Solved

SQL Server R2 Stored procedure performance

Posted on 2016-11-01
8
33 Views
Last Modified: 2016-11-25
Hi Experts,

The performance on this SP is not good and I am looking for ways to make much better:
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
 
Declare @DB_SEVERITY as varchar(128)
Set @DB_SEVERITY = @SEVERITY
 
DECLARE @TOP as date
SET @TOP = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates order BY ScanDate desc) --Jan
Declare @NEXT1 as date
Set @NEXT1 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @TOP order BY ScanDate desc) ---Dec
Declare @NEXT2 as date
Set @NEXT2 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT1 order BY ScanDate desc)  ---NOv
Declare @NEXT3 as date
Set @NEXT3 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT2 order BY ScanDate desc) ; ---Oct
Declare @NEXT4 as date
Set @NEXT4 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT3 order BY ScanDate desc) ; ---Sept
Declare @NEXT5 as date
Set @NEXT5 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT4 order BY ScanDate desc)  ---Aug
Declare @NEXT6 as date
Set @NEXT6 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT5 order BY ScanDate desc) ; ---July
Declare @NEXT7 as date
Set @NEXT7 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT6 order BY ScanDate desc) ; ---June
Declare @NEXT8 as date
Set @NEXT8 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT7 order BY ScanDate desc)  ---May
Declare @NEXT9 as date
Set @NEXT9 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT8 order BY ScanDate desc) ; ---April
Declare @NEXT10 as date
Set @NEXT10 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT9 order BY ScanDate desc) ; ---March
Declare @NEXT11 as date
Set @NEXT11 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT10 order BY ScanDate desc)  ---Feb
Declare @NEXT12 as date
Set @NEXT12 = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates where ScanDate < @NEXT11 order BY ScanDate desc) ; ---Jan
 
IF OBJECT_ID('TempDB..#tmpENT_REM_EFFORT_CTE') IS NOT NULL
              DROP TABLE #tmpENT_REM_EFFORT_CTE;
 
CREATE TABLE #tmpENT_REM_EFFORT_CTE(
       [ScanDate] [date] NOT NULL,
       [iplong] [bigint] NULL,
       [plugin] [int] NOT NULL,
       [severity] [varchar](255) NULL,
          [Branch Region] [varchar] (128) NULL,
          [Branch Network] [varchar] (128) NULL,
          [Branch] [varchar] (128) NULL
)
 
INSERT INTO #tmpENT_REM_EFFORT_CTE
           ([ScanDate]
           ,[iplong]
           ,[plugin]
           ,[severity]
                ,[Branch Region]
                ,[Branch Network]
                ,[Branch]
)
 
       select ss.ScanDate,  ss.iplong, ss.Plugin, vn.severity, org.[Branch Region], org.[Branch Network], org.Branch
       
       from Dflt.Systems AS ss 
       inner join (select ScanDate from Dflt.Scandates where ScanDate = @TOP or  ScanDate = @NEXT1 or  ScanDate = @NEXT2 or ScanDate = @NEXT3 or ScanDate = @NEXT4
                or  ScanDate = @NEXT5 or  ScanDate = @NEXT6 or ScanDate = @NEXT7 or ScanDate = @NEXT8 or  ScanDate = @NEXT9 or  ScanDate = @NEXT10 or ScanDate = @NEXT11 
                or ScanDate = @NEXT12) as sd on sd.Scandate = ss.Scandate
       Inner Join dflt.Vulnerabilities as vn on ss.Plugin = vn.Plugin
          inner join Dflt.Ownership_Systems as own on own.SystemsID = ss.SystemsID and  own.ScandateID = ss.scandateID
       left outer JOIN Dflt.Organizations as Org on Org.OrganizationID = Own.OrganizationID
       
       WHERE vn.Severity =  @DB_SEVERITY
 
----QUERY
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch, 
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],        
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT12 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT11
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT10
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT9
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT8
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT7
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT6
 
where sa.ScanDate = @NEXT12
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region] 
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],         
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT11 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT10
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT9
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT8
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT7
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT6
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT5
 
where sa.ScanDate = @NEXT11
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],         
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT10 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT9
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT8
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT7
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT6
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT5
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT4
where sa.ScanDate = @NEXT10
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],          
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT9 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT8
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT7
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT6
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT5
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT4
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT3
where sa.ScanDate = @NEXT9
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],        
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT8 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT7
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT6
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT5
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT4
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT3
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT2
where sa.ScanDate = @NEXT8
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],        
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT7 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT6
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT5
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT4
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT3
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT2
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @NEXT1
where sa.ScanDate = @NEXT7
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150],
sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 180],       
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT6 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
cast(sum (case when (nn6.iplong is null and nn6.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT5
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT4
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT3
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT2
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @NEXT1
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn6 on nn6.iplong = sa.iplong and nn6.plugin = sa.plugin and nn6.scandate = @TOP
where sa.ScanDate = @NEXT6
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region],  sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],     
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 150], 
0 as [Resolved_Vulnerabilities in 180],       
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT5 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
cast(sum (case when (nn5.iplong is null and nn5.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 150],
0 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT4
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT3
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT2
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @NEXT1
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn5 on nn5.iplong = sa.iplong and nn5.plugin = sa.plugin and nn5.scandate = @TOP
where sa.ScanDate = @NEXT5
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],      
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 120],
0 as [Resolved_Vulnerabilities in 150],
0 as [Resolved_Vulnerabilities in 180],       
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT4 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
cast(sum (case when (nn4.iplong is null and nn4.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 120],
0 as [Percent 150],
0 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT3
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT2
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @NEXT1
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn4 on nn4.iplong = sa.iplong and nn4.plugin = sa.plugin and nn4.scandate = @TOP
where sa.ScanDate = @NEXT4
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities]  , sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 90],
0 as [Resolved_Vulnerabilities in 120],
0 as [Resolved_Vulnerabilities in 150],
0 as [Resolved_Vulnerabilities in 180],       
--case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
case when sa.scandate = @NEXT3 then 'MARK' else null end  as [Marker],
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
cast(sum (case when (nn3.iplong is null and nn3.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 90],
0 as [Percent 120],
0 as [Percent 150],
0 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate = @NEXT2
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @NEXT1
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn3 on nn3.iplong = sa.iplong and nn3.plugin = sa.plugin and nn3.scandate = @TOP
where sa.ScanDate = @NEXT3
group by sa.scandate,  sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
Union all
 
select sa.ScanDate,  count(sa.scandate) as [Total_Vulnerabilities] , sa.severity,  sa.[Branch Region], sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
       sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
       sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 60],
       0 as [Resolved_Vulnerabilities in 90],
          0 as [Resolved_Vulnerabilities in 120],
       0 as [Resolved_Vulnerabilities in 150],
          0 as [Resolved_Vulnerabilities in 180],
       case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
       cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
       cast(sum (case when (nn2.iplong is null and nn2.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 60],
       0 as [Percent 90],
          0 as [Percent 120],
       0 as [Percent 150],
          0 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate =  @NEXT1
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn2 on nn2.iplong = sa.iplong and nn2.plugin = sa.plugin and nn2.scandate = @TOP
 
where sa.ScanDate = @NEXT2
group by sa.scandate , sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
union  all
 
select sa.ScanDate,   count(sa.scandate) as [Total_Vulnerabilities] ,sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch,
(Select count(Distinct iplong) from #tmpENT_REM_EFFORT_CTE as hh where hh.scandate = sa.scandate and hh.[Branch Region] = sa.[Branch Region]
and hh.[Branch Network] = sa.[Branch Network] and hh.[Branch] = sa.[Branch]) as [Total distinct devices with a critical vulnerability],
       sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as [Resolved_Vulnerabilities in 30],
       0 as [Resolved_Vulnerabilities in 60],
       0 as [Resolved_Vulnerabilities in 90],
          0 as [Resolved_Vulnerabilities in 120],
       0 as [Resolved_Vulnerabilities in 150],
          0 as [Resolved_Vulnerabilities in 180],
       case when sa.scandate = (Select min(scandate) from #tmpENT_REM_EFFORT_CTE) then 'MARK' else null end  as [Marker],
       cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],
       0 as [Percent 60],
       0 as [Percent 90],
          0 as [Percent 120],
       0 as [Percent 150],
          0 as [Percent 180]
 
from #tmpENT_REM_EFFORT_CTE as sa
 
Left outer join (Select iplong, plugin, scandate from #tmpENT_REM_EFFORT_CTE ) as nn1 on nn1.iplong = sa.iplong and nn1.plugin = sa.plugin and nn1.scandate =  @TOP
where sa.ScanDate = @NEXT1
group by sa.scandate , sa.severity, sa.[Branch Region], sa.[Branch Network], sa.Branch
 
                    
END

Open in new window

Please I am only looking for better performance.

Thank You....
0
Comment
Question by:Amour22015
8 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41869332
This is like asking someone to repair your car by sending them a picture of it.  You're going to need to provide at the minimum an execution plan and IO data.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41869384
Hi Author,

Your procedure is very big. There is a lot of scope of improvement.

1. Why are using distinct and Order by in starting 10 queries? Do you really need it?
2.  You have used lot of UNION ALLs, which also i think we can remove.

Execution plan would have helped. Basically Its an entire project.

Can you give the entire data , table schema and current output ?
0
 

Author Comment

by:Amour22015
ID: 41869417
Hi and thanks,

Looks like I am being miss understood.

I am looking for information on how to improve the performance of the current query.

Example:
Would having all the:
Declare statements together help?
DECLARE
@TOP as date
@NEXT1 as date
etc....

And the Set statements:
Set
@DB_SEVERITY = @SEVERITY
@TOP = (Select DISTINCT Top 1 ScanDate from dflt.ScanDates order BY ScanDate desc) --Jan
etc....

What about the Sum/cast Statements into some kind of loop?

the Union All Statements also need improving.

Basically need help cleaning this query up.

Like:
There is a lot of scope of improvement
This is true and what I am looking for.

Actually yes this is like having a picture of a car and being asked to clean it up.  Like looking at a picture and seeing a dirty car that needs to have all the dirt washed out from causing the wheels to rotate slowly.  Or what an insurance agent does when there is an accident, they are not concerned with what is under the hood.  Notice I am asking only for you to repair what you see.   I don't even know what the "execution plan" would be, nor do I even care.  All I care about is that it comes out with the same amount of data but is faster.  So I was asked to repair the picture and the only thing I know is that this query is slow and that is the only reason.  To make this clear the current query works but is just slow.   Please lets just think that all under the car is working.  All the table(s) involved have been indexed.

Please help and thank you...
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41869498
Is it possible to give us the execution plan? I am going through query , Shall provide you the pointers.

Thank you !
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Amour22015
ID: 41871181
Wow,

this is the first time I even heard about a "execution plan".  So I looked it up but am even more confused.  So how can I get an "execution plan"? seems like that is another posting before I can do this one.

Thanks...
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41871448
Include the execution plan using Ctrl + M or use below and run the query !

EE

Hope it helps !!
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 41873454
Example:
Would having all the:
Declare statements together help?
DECLARE
@TOP as date
@NEXT1 as date
Such things are practically irrelevant as far as performance goes. When performance is an issue, the first things needed are about the database definitions. You could supply all related table/view/index definitions (plus some basic descriptions such as data volumes, e.g., how many rows in the tables and what the various column data types/sizes are), but an "execution plan" will give most of that info in a neat package.

We have no idea what your database is like.

Some tables might simply be a couple dozen rows of codes and descriptions. Other tables might have millions of rows with numerous columns that are 2k bytes wide and have no indexes, or every table might have a dozen or more associated indexes. Some of your "table" references might be complex views that join a dozen physical tables.

We simply don't know. But an "execution plan" will give a lot of those clues to us as far as this particular query goes, and we won't have to ask a few dozen questions just to learn what performance improvements might even be possible. For all we know, the current performance might be almost as good as it will ever be. (You haven't even described what the current "performance" is right now.)

As it is, you're asking a question like this: "I'm doing A, B, C and D in my store. What can I do using only material from my store to handle customers faster." Unfortunately, we don't know what you have in your store nor how it's organized. If you're selling truck tires, the answer could be quite different for a hardware or food store.
0
 

Author Closing Comment

by:Amour22015
ID: 41901705
Ok good thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now