Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

SQL Server 2012 r2 - calculations/operation on many Temp Tables

Hi Experts,

Currently I am working on reducing the time each temp table will take:

Temp Tables each on has: Scandate, Category, Plugin, IPLong as fields:
##tmpBaseline
##tmpSpecial
##tmpAdobe
##tmpJava
##tmpMicrosoft
##tmpetc......
Each one of the temp tables has many records, having Month1, Month2, Month3, Month4, Month5, Month6 and the records are to be counted for the next step.

So for this posting.  My question relates to calculations/operation.  I know the below attempt query is not correct nor is the logic.

Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc



So:
BL = BaseLine on chart
SP - BL= Special on Chart
AB - BL- SP(less non Special)) = Adobe on Chart

Maybe like:
From MS
WHERE Plugin NOT IN
    ((SELECT SP.Plugin From SP) And (Select BL.Plugin From BL))

Open in new window


Because each temp table has for each month I will also need to include something like:

Scandate = (select top 1 Scandate From BL) as Month6
and so on....

The Chart will be developed in SSRS the attachment is excel example please see chart.

Please help and thanks...
Chart.xlsx
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Armour22015,

Well for this case you should either use JOIN, Cross Apply or NOT EXISTS. In are slow in nature.

NOT EXISTS is much better if you are just checking for the row existence. It will exit on the first row itself from the right hand side.

Code will look like below-

--
From MS
WHERE Plugin 
NOT EXISTS
(
		(SELECT NULL From SP WHERE SP.Plugin = MS.Plugin) 
	AND (SELECT NULL FROM BL WHERE BL.Plugin = MS.Plugin)
)
--

Open in new window


If you want fetch data from Right hand table then you should either use JOIN or Cross Apply.

Scandate = (select top 1 Scandate From BL) as Month6
and so on.... 

Open in new window


For the above code always use ORDER BY like below. I also suspect that you can use Cross Apply in the case.

--
select top 1 Scandate From BL ORDER BY ScanDate DESC
--

Open in new window


Please provide the entire query so that we can check and provide you all the performance tips.

Hope it helps!
Avatar of Amour22015
Amour22015

ASKER

Hi Experts,

I have not written the whole Stored Procedure yet.  Thus the reason for this post.  I am looking for help to finish this query with:
calculations/operation.

Based on:
Presidencies of order:
##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc


Please help and thanks
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Experts,

So far what I have:
CREATE TABLE #tmpbaseline(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Specialized Catagory
IF OBJECT_ID('TempDB..#tmpSpecialized') IS NOT NULL
        DROP TABLE #tmpSpecialized;

CREATE TABLE #tmpSpecialized(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Marker-File (Java) 
IF OBJECT_ID('TempDB..#tmpMarkerFile') IS NOT NULL
        DROP TABLE #tmpMarkerFile;

CREATE TABLE #tmpMarkerFile(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Java Category
IF OBJECT_ID('TempDB..#tmpJava') IS NOT NULL
        DROP TABLE #tmpJava;

CREATE TABLE #tmpJava(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Adobe Category
IF OBJECT_ID('TempDB..#tmpAdobe') IS NOT NULL
        DROP TABLE #tmpAdobe;

CREATE TABLE #tmpAdobe(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Microsoft Category
IF OBJECT_ID('TempDB..#tmpMicrosoft') IS NOT NULL
        DROP TABLE #tmpMicrosoft;

CREATE TABLE #tmpMicrosoft(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- MSXML Category
IF OBJECT_ID('TempDB..#tmpMSXML') IS NOT NULL
        DROP TABLE #tmpMSXML;

CREATE TABLE #tmpMSXML(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)

-- ************************************************ Categories **********************************************************************************

-- Above Baseline Section
Insert into #tmpbaseline (Scandate, Category, Plugin, IPLong)

SELECT 
		rs.[ScanDate]
		,'Above Baseline' as Category
		,rs.[Plugin]
		,rs.IPLong
      
FROM [Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
		Inner Join dflt.scandates as sd on rs.ScanDateID = sd.ID 
		Left outer join [Dflt].[OperatingSystemGroups] as osg on osg.OSGroupID = rs.OSGroupID 
		Inner Join [FODW_PVT].dflt.[F_VAResponseActionItemNoDups]() vra 
			On rs.Plugin = vra.Plugin and osg.MajorGrouping = vra.Operating_Sys and sd.ID = vra.ScanDateID
 
  
where [Actionable] = 0 and vra.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High')

Group By rs.[ScanDate], rs.[Plugin], rs.IPLong

-- 81,608 Dev
-- 2.03 sec Dev
-- 948,063 Prod
-- 1.52 sec Prod
-- 1,909,563 1.16 sec Based on 6 months in Dev as of 1/31/17
-- End Above_basline




-- Specialized Catagory Section

Insert into #tmpSpecialized (Scandate, Category, Plugin, IPLong)
SELECT 
		rs.[ScanDate]
		,'Specialized' as Category
		,rs.Plugin
		,rs.IPLong
           
From Dflt.Systems as rs
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin  
		Left Outer join Dflt.ADComputers as adc on adc.ComputerName = rs.HostName

WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc) and (adc.OUPath like '%Specialized Systems%'
		Or adc.OUPath like '%Medical%')  and vn.Severity in ('Critical', 'High') --and [Actionable] = 1
  
Group By rs.ScanDate, Rs.Plugin, rs.IPLong

-- Specialized **************************
-- Month of Dec
-- 291,337 Prod
-- .19 sec Prod Or
-- All 6 months
-- 1,719,802 Prod
-- .40 sec Prod
-- 1,707,945 records 1.08 sec in Dev as of 1/31/17
-- End Specialized Category




-- Marker-file (Java) Category Section *** Note: Still needs a lot of work... as of 1/25/17, need to add in checks for previous inputs to report
Insert into #tmpMarkerFile (Scandate, Category, Plugin, IPLong)

select
		Scandate
		,'Marker File (Java)' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs
		inner join [Dflt].[SCCM_v_R_System] as s on s.iplong = rs.iplong
		inner join [Dflt].[SCCM_Resource_MarkerFile] as rm on s.ResourceID = rm.ResourceID and s.SCCM_DB = rm.SCCM_DB
		inner join [Dflt].[SCCM_MarkerFile] as m on m.MarkerFileID = rm.MarkerFileID 
		inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
		inner Join [Dflt].[OperatingSystemGroups] OSG On s.OSGroupID = OSG.OSGroupID
		left outer join [Dflt].[Ownership_Systems] as Own on Own.SystemsID = rs.SystemsID and rs.ScanDateID = own.ScandateID
		left outer join Dflt.Organizations as Org on Org.OrganizationID = Own.OrganizationID
  
Where
		m.MarkerFileName like '%java%'  
		and vul.[plugin name]  like '%java%'
		and rs.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
		and vul.Severity in ('Critical', 'High') 

Group By Scandate, rs.plugin, rs.IPLong

-- Marker File (Java)
-- Dec
-- 90,177 Prod
-- .3 sec Or
-- 6 month
-- 418,469 Prod
-- .15 sec
-- 418,469 records .19 sec in Dev as on 1/31/17
-- End Marker-file (Java)

-- ***************** Application Catalog *******************************

-- Java Category Section
Insert into #tmpJava (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Java' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT distinct Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category = 'Java') as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- Java Category
-- Dec
-- 45,135 Prod
-- .1 sec Or
-- 6 month
-- 287,579 Prod
-- .8 sec
-- 287,477 records .10 sec in Dev as of 1/31/17
-- End Java Category


-- Adobe Category Section
Insert into #tmpAdobe (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Adobe' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category like '%Adobe%'
		) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- Adobe Category
-- Dec
-- 952,149 Prod
-- .26 sec Or
-- 6 month
-- 4,830,682 Prod
-- 2.24 sec
-- Temp table
-- 335 records
-- 4.02 sec
-- 4,829,949 7.21 sec in Dev as of 1/31/17
-- End Adobe Category



-- Microsoft Category  ****** Note this section needs updating see notes ************* Takes way too long to render 1/27/17

Insert into #tmpMicrosoft (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Microsoft' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT distinct Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category In ('Microsoft (Non-Patch)', 'Microsoft (Patch)')
		) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- Microsoft Category
-- Dec
-- 952,149 Prod
-- .26 sec Or
-- 6 month
-- 4,830,682 Prod
-- 2.24 sec
-- Temp table
-- 335 records
-- 4.02 sec
-- 7,867,469 13.57 Sec in Dev as of 1/31/17
-- Takes way too long to render *****************************************************
  -- End Microsoft Category


-- MSXML Category  ***** Note: Did not find "MSXML" in CVA_App_Catalog.Category Table


Insert into #tmpMSXML (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'MSXML' as Category 
		,rs.[Plugin]
		,IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs
		inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin

  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vul.Severity in ('Critical', 'High') and vul.[plugin name] = 'Microsoft XML Parser (MSXML) and XML Core Services Unsupported'

Group By ScanDate, rs.Plugin, rs.IPLong

-- MSXML Category
-- 233,818 Records 1.47 sec in Dev as of 1/31/17
-- End MSXML Category

Open in new window


Based on:
 Presidencies of order:
#tmpSpecial SP = 250,000
 #tmpBaseline BL = 100,000 Records
#tmpMarker MK = 200,000
 #Adobe AB = 500,000
 #tmpJava JA = 1,000,000
 #tmpMicrosoft MS = 600,000
 #tmpMSXML MSX = 2,000


So this is the order:
SP: Special = Special on Chart
BL: ABOVE BASELINE – SPECIALIZED = ABOVE BASELINE ON CHART
AB: ADOBE – ABOVE BASELINE – SPECIALIZED = ADOBE ON CHART
MSX: MSXML –  ABOVE BASELINE – SPECIALIZED = MSXML ON CHART
MS: MICROSOFT – ABOVE BASELINE – SPECIALIZED –  MSXML = MICROSOFT ON CHART
JA: JAVA – ABOVE BASELINE – SPECIALIZED  – MARKER FILE  - Microsoft = ACTIONABLE JAVA ON CHART
MK: Marker - JAVA – ABOVE BASELINE – SPECIALIZED - Microsoflt = MARKER FILE ON CHART
TOTAL VULNERABILITIES – ABOVE BASELINE – SPECIALIZED – ALL CATEGORIES = OTHERS ON CHART

So something like:
2:From MS
3:WHERE Plugin 
4:NOT EXISTS
5:(
6:		(SELECT NULL From SP WHERE SP.Plugin = MS.Plugin And SP.IPLong = MS.IPLong And SP.Scandate = MS.Scandate) 
7:	AND (SELECT NULL FROM BL WHERE BL.Plugin = MS.Plugin And SP.Scandate = MS.Scandate And SP.IPLong = MS.IPLong )
8:)

Open in new window


Please help and thanks
Hi Experts,

So far I have this:
USE FODW_PVT
-- Note SP: [App].[usp_RP_SMA_Division_6monthVulnCategoryAction]



--In the case where  “CAREMANAGEMENT “ is not populated

--ABOVE BASELINE – SPECIALIZED = ABOVE BASELINE ON CHART
--ADOBE – ABOVE BASELINE – SPECIALIZED = ADOBE ON CHART
--MSXML – ABOVE BASELINE – SPECIALIZED = MSXML ON CHART
--MICROSOFT – ABOVE BASELINE – SPECIALIZED –  MSXML = MICROSOFT ON CHART 
--JAVA – ABOVE BASELINE – SPECIALIZED  – MARKER FILE = ACTIONABLE JAVA ON CHART
--JAVA – ABOVE BASELINE – SPECIALIZED –  ACTIONABLE JAVA = MARKER FILE ON CHART
--TOTAL VULNERABILITIES – ABOVE BASELINE – SPECIALIZED – ALL CATEGORIES = OTHERS ON CHART


-- turn into Temp Table if required filters are needed example Microsoft which category: If in Above basline, Specialized, Etc...
-- Categories:
--MSXML
--Microsoft
--Adobe
--Above Baseline
--Java
--Specialized
--Other
--Marker-file (Java)
-- Above_basline Category Note: bring temp table to end and do more testing... 1/27/17
-- top part needs to go to the every top

-- Temp Table Create/Drop Section:
-- Above Baseline
IF OBJECT_ID('TempDB..#tmpbaseline') IS NOT NULL
        DROP TABLE #tmpbaseline;

CREATE TABLE #tmpbaseline(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Specialized Catagory
IF OBJECT_ID('TempDB..#tmpSpecialized') IS NOT NULL
        DROP TABLE #tmpSpecialized;

CREATE TABLE #tmpSpecialized(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Marker-File (Java) 
IF OBJECT_ID('TempDB..#tmpMarkerFile') IS NOT NULL
        DROP TABLE #tmpMarkerFile;

CREATE TABLE #tmpMarkerFile(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Java Category
IF OBJECT_ID('TempDB..#tmpJava') IS NOT NULL
        DROP TABLE #tmpJava;

CREATE TABLE #tmpJava(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Adobe Category
IF OBJECT_ID('TempDB..#tmpAdobe') IS NOT NULL
        DROP TABLE #tmpAdobe;

CREATE TABLE #tmpAdobe(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- Microsoft Category
IF OBJECT_ID('TempDB..#tmpMicrosoft') IS NOT NULL
        DROP TABLE #tmpMicrosoft;

CREATE TABLE #tmpMicrosoft(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)
-- MSXML Category
IF OBJECT_ID('TempDB..#tmpMSXML') IS NOT NULL
        DROP TABLE #tmpMSXML;

CREATE TABLE #tmpMSXML(
 Scandate Date NULL,
 Category [varchar](255) NULL,
 Plugin int,
 IPLong Bigint NULL, 
)

-- ************************************************ Categories **********************************************************************************

-- Above Baseline Section
Insert into #tmpbaseline (Scandate, Category, Plugin, IPLong)

SELECT 
		rs.[ScanDate]
		,'Above Baseline' as Category
		,rs.[Plugin]
		,rs.IPLong
      
FROM [Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin
		Inner Join dflt.scandates as sd on rs.ScanDateID = sd.ID 
		Left outer join [Dflt].[OperatingSystemGroups] as osg on osg.OSGroupID = rs.OSGroupID 
		Inner Join [FODW_PVT].dflt.[F_VAResponseActionItemNoDups]() vra 
			On rs.Plugin = vra.Plugin and osg.MajorGrouping = vra.Operating_Sys and sd.ID = vra.ScanDateID
 
  
where [Actionable] = 0 and vra.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High')

Group By rs.[ScanDate], rs.[Plugin], rs.IPLong

-- 1,909,563 1.16 sec Based on 6 months in Dev as of 1/31/17
-- End Above_basline




-- Specialized Catagory Section

Insert into #tmpSpecialized (Scandate, Category, Plugin, IPLong)
SELECT 
		rs.[ScanDate]
		,'Specialized' as Category
		,rs.Plugin
		,rs.IPLong
           
From Dflt.Systems as rs
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin  
		Left Outer join Dflt.ADComputers as adc on adc.ComputerName = rs.HostName

WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc) and (adc.OUPath like '%Specialized Systems%'
		Or adc.OUPath like '%Medical%')  and vn.Severity in ('Critical', 'High') --and [Actionable] = 1
  
Group By rs.ScanDate, Rs.Plugin, rs.IPLong

-- 1,707,945 records 1.08 sec in Dev as of 1/31/17
-- End Specialized Category




-- Marker-file (Java) Category Section
Insert into #tmpMarkerFile (Scandate, Category, Plugin, IPLong)

select
		Scandate
		,'Marker File (Java)' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs
		inner join [Dflt].[SCCM_v_R_System] as s on s.iplong = rs.iplong
		inner join [Dflt].[SCCM_Resource_MarkerFile] as rm on s.ResourceID = rm.ResourceID and s.SCCM_DB = rm.SCCM_DB
		inner join [Dflt].[SCCM_MarkerFile] as m on m.MarkerFileID = rm.MarkerFileID 
		inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin
		inner Join [Dflt].[OperatingSystemGroups] OSG On s.OSGroupID = OSG.OSGroupID
		left outer join [Dflt].[Ownership_Systems] as Own on Own.SystemsID = rs.SystemsID and rs.ScanDateID = own.ScandateID
		left outer join Dflt.Organizations as Org on Org.OrganizationID = Own.OrganizationID
  
Where
		m.MarkerFileName like '%java%'  
		and vul.[plugin name]  like '%java%'
		and rs.scandateid in (Select top (6 ) id from dflt.scandates order by id desc)
		and vul.Severity in ('Critical', 'High') 

Group By Scandate, rs.plugin, rs.IPLong

-- 418,469 records .19 sec in Dev as on 1/31/17
-- End Marker-file (Java)

-- ***************** Application Catalog *******************************

-- Java Category Section
Insert into #tmpJava (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Java' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT distinct Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category = 'Java') as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- 287,477 records .10 sec in Dev as of 1/31/17
-- End Java Category


-- Adobe Category Section
Insert into #tmpAdobe (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Adobe' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category like '%Adobe%'
		) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- 4,829,949 7.21 sec in Dev as of 1/31/17
-- End Adobe Category



-- Microsoft Category Section
Insert into #tmpMicrosoft (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'Microsoft' as Category 
		,rs.[Plugin]
		,rs.IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs 
		INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
		INNER JOIN (SELECT distinct Plugin, Category
		FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
		Where Category In ('Microsoft (Non-Patch)', 'Microsoft (Patch)')
		) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

-- 7,867,469 13.57 Sec in Dev as of 1/31/17
-- Takes way too long to render *****************************************************
-- End Microsoft Category


-- MSXML Category Section
Insert into #tmpMSXML (Scandate, Category, Plugin, IPLong)

SELECT 
		Scandate
		,'MSXML' as Category 
		,rs.[Plugin]
		,IPLong

FROM [FODW_PVT].[Dflt].[Systems] as  rs
		inner join [Dflt].[Vulnerabilities] as vul on vul.Plugin = rs.Plugin

  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
		and vul.Severity in ('Critical', 'High') and vul.[plugin name] = 'Microsoft XML Parser (MSXML) and XML Core Services Unsupported'

Group By ScanDate, rs.Plugin, rs.IPLong

-- MSXML Category
-- 233,818 Records 1.47 sec in Dev as of 1/31/17
-- End MSXML Category




-- **********************  Calculations *******************************

Select
		SL.Scandate, 
		SL.Category, 
		SL.Plugin, 
		SL.IPLong,
		Count(*) as Total
From #tmpSpecialized SL -- Specialized

UNION ALL

Select
		AB.Scandate, 
		AB.Category, 
		AB.Plugin, 
		AB.IPLong,
		Count(*) as Total 
From #tmpbaseline AB -- Above Base line
Where --AB.Plugin, AB.IPLong
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (AB.Plugin = SL.Plugin And AB.IPLong = SL.IPLong And AB.Scandate = SL.Scandate)
)

UNION ALL

--- Temp Table 
Select
		MF.Scandate, 
		MF.Category, 
		MF.Plugin, 
		MF.IPLong,
		Count(*) as Total 
From #tmpMarkerFile MF -- Marker File (Java)
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MF.Plugin = SL.Plugin And MF.IPLong = SL.IPLong And MF.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB  Where (MF.Plugin = AB.Plugin And MF.IPLong = AB.IPLong And MF.Scandate = AB.Scandate)
)

UNION ALL

 ---- Temp Table 
Select
		JA.Scandate, 
		JA.Category, 
		JA.Plugin, 
		JA.IPLong,
		Count(*) as Total 
From #tmpJava JA -- Java Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (JA.Plugin = SL.Plugin And JA.IPLong = SL.IPLong And JA.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB  Where (JA.Plugin = AB.Plugin And JA.IPLong = AB.IPLong And JA.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF  Where (JA.Plugin = MF.Plugin And JA.IPLong = MF.IPLong And JA.Scandate = MF.Scandate)
)

UNION ALL

 -- Temp Table 
Select
		AD.Scandate, 
		AD.Category, 
		AD.Plugin, 
		AD.IPLong,
		Count(*) as Total 
From #tmpAdobe AD -- Adobe Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (AD.Plugin = SL.Plugin And AD.IPLong = SL.IPLong And AD.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB  Where (AD.Plugin = AB.Plugin And AD.IPLong = AB.IPLong And AD.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF  Where (AD.Plugin = MF.Plugin And AD.IPLong = MF.IPLong And AD.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA  Where (AD.Plugin = JA.Plugin And AD.IPLong = JA.IPLong And AD.Scandate = JA.Scandate)
)

UNION ALL

-- Temp Table 
Select
		MS.Scandate, 
		MS.Category, 
		MS.Plugin, 
		MS.IPLong,
		Count(*) as Total 
From #tmpMicrosoft MS -- Microsoft Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MS.Plugin = SL.Plugin And MS.IPLong = SL.IPLong And MS.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB  Where (MS.Plugin = AB.Plugin And MS.IPLong = AB.IPLong And MS.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF  Where (MS.Plugin = MF.Plugin And MS.IPLong = MF.IPLong And MS.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA  Where (MS.Plugin = JA.Plugin And MS.IPLong = JA.IPLong And MS.Scandate = JA.Scandate)
UNION
SELECT NULL FROM #tmpAdobe AD  Where (MS.Plugin = AD.Plugin And MS.IPLong = AD.IPLong And MS.Scandate = AD.Scandate)
)

UNION ALL

-- Temp Table 
Select
		MSX.Scandate, 
		MSX.Category, 
		MSX.Plugin, 
		MSX.IPLong,
		Count(*) as Total 
From #tmpMSXML MSX -- MSXML Category
Where
NOT EXISTS
(
SELECT NULL FROM #tmpSpecialized SL Where (MSX.Plugin = SL.Plugin And MSX.IPLong = SL.IPLong And MSX.Scandate = SL.Scandate)
UNION
SELECT NULL FROM #tmpbaseline AB  Where (MSX.Plugin = AB.Plugin And MSX.IPLong = AB.IPLong And MSX.Scandate = AB.Scandate)
UNION
SELECT NULL FROM #tmpMarkerFile MF  Where (MSX.Plugin = MF.Plugin And MSX.IPLong = MF.IPLong And MSX.Scandate = MF.Scandate)
UNION
SELECT NULL FROM #tmpJava JA  Where (MSX.Plugin = JA.Plugin And MSX.IPLong = JA.IPLong And MSX.Scandate = JA.Scandate)
UNION
SELECT NULL FROM #tmpAdobe AD  Where (MSX.Plugin = AD.Plugin And MSX.IPLong = AD.IPLong And MSX.Scandate = AD.Scandate)
UNION
SELECT NULL FROM #tmpMicrosoft MS  Where (MSX.Plugin = MS.Plugin And MSX.IPLong = MS.IPLong And MSX.Scandate = MS.Scandate)
)

Open in new window


I also added:
Group By MSX.Scandate, 
		MSX.Category, 
		MSX.Plugin, 
		MSX.IPLong

Open in new window


On each end of the Select Statements (got errors otherwise).

Thank you for helping...
Great Thanks for helping