Avatar of Robb Hill
Robb Hill
Flag for United States of America asked on

Stored Proc - SQL Server

have two sub queires in this stored proc....and its giving me row issues...the data comes out like this.  I am pasting the entire sp.

date               total1  date               total2
05/12/2011      1      05/12/2011      1
06/09/2011      2      05/12/2011      1
06/23/2011      17      05/12/2011      1
06/30/2011      137      05/12/2011      1
05/12/2011      1      06/09/2011      2
06/09/2011      2      06/09/2011      2
06/23/2011      17      06/09/2011      2
06/30/2011      137      06/09/2011      2


Somehow I need this to pivot on Date....so it would look like this.

date           total1   total2

5/12/2011  1          1
6/9/2011    2           1
6/23/2011  17         null or 0
6/30/2011  137       null or 0
Create Proc DSWX_VulnRatingSummary (@Rating varchar(50), @Aging int)
AS
BEGIN


DECLARE @Nth int, @MaxGoBack int, @Today datetime

SET @Nth = 7
SET @MaxGoBack = 90
SET @Today = DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')

select  TotalsForRating.DateDetected, TotalsForRating.Total as Rating, TotalsForAging.total  as total

from 

(
SELECT t3.DateDetected, COUNT(t3.VulnName) AS Total

from

(

select 
	   t2.DeviceName,
	   t2.IPAddress,
	   t2.VulnName,
	   t2.VulnDescription,
	   t2.ScanSource,
	   convert(varchar,t2.DateDetected,101) as DateDetected, 
	   t2.Aging,
	   t2.AgingDays,
	   t2.DSWX_VulnRating,
	   t2.DSWX_VulnRatingDesc,
	   t2.VendorSeverity,
	   t2.VendorSeverityValue
	  


from



(select t1.DeviceName,
		t1.IPAddress,		
	    t1.VulnID,
	    t1.VulnName,
	    t1.VulnDescription,
	    t1.ScanSource,
	    t1.VendorSeverity,
	    t1.VendorSeverityValue,
	   CASE 
         WHEN t1.DSWX_VulnRating >= 22 THEN 'Critical'
         WHEN t1.DSWX_VulnRating between 20 and 21 THEN 'High'
         WHEN t1.DSWX_VulnRating between 18 and 19 THEN 'Medium'
         WHEN t1.DSWX_VulnRating between 0 and 17 THEN 'Low'
         ELSE 'n/a'
      END as DSWX_VulnRatingDesc, 
      t1.DSWX_VulnRating, 
      CASE	
         WHEN t1.AgingDays > 14  THEN '14'
         WHEN t1.AgingDays > 30  THEN '30'
         WHEN t1.AgingDays > 60  THEN '60'
         WHEN t1.AgingDays  > 90 THEN '90'
         ELSE '0'
      END as Aging,
      t1.AgingDays,
      t1.DateDetected
   


from
(SELECT     h.DeviceName, 
		   h.IPAddress,
		   v.VulnID, 
		   v.VulnName, 
           v.VulnDescription, 
           h.RiskRatingAsset, 
           v.RiskRatingVuln,
           sc.Value AS VendorSeverityValue, 
           h.RiskRatingCounter AS RiskCounterMeasuresRating,
           h.RiskRatingAsset + sc.Value + v.RiskRatingVuln - h.RiskRatingCounter  AS DSWX_VulnRating, 
           v.Severity AS VendorSeverity, 
           v.DateDetected, 
           v.AgingDays, 
           v.ScanSource
FROM         dbo.SWRX_SeverityConversionTable  sc RIGHT OUTER JOIN  dbo.SWRX_MasterVulnerabilityList v  
					ON sc.Severity = v.Severity 
			 RIGHT OUTER JOIN  dbo.SWRX_MasterHost h
			  ON v.DeviceName = h.DeviceName 
WHERE     (NOT (v.VulnName LIKE 'CD-%'))
)t1

)t2

where t2.ScanSource <> 'Sherlock'
)t3

WHERE t3.DSWX_VulnRatingDesc = @Rating 
	and t3.DateDetected < @Today AND
    t3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
    (DATEDIFF(day, @Today, t3.DateDetected) % @Nth) = 0
group by t3.DateDetected
) as TotalsForRating,

(
SELECT t3.DateDetected, COUNT(t3.VulnName) AS Total

from

(

select 
	   t2.DeviceName,
	   t2.IPAddress,
	   t2.VulnName,
	   t2.VulnDescription,
	   t2.ScanSource,
	   convert(varchar,t2.DateDetected,101) as DateDetected, 
	   t2.Aging,
	   t2.AgingDays,
	   t2.DSWX_VulnRating,
	   t2.DSWX_VulnRatingDesc,
	   t2.VendorSeverity,
	   t2.VendorSeverityValue
	  


from



(select t1.DeviceName,
		t1.IPAddress,		
	    t1.VulnID,
	    t1.VulnName,
	    t1.VulnDescription,
	    t1.ScanSource,
	    t1.VendorSeverity,
	    t1.VendorSeverityValue,
	   CASE 
         WHEN t1.DSWX_VulnRating >= 22 THEN 'Critical'
         WHEN t1.DSWX_VulnRating between 20 and 21 THEN 'High'
         WHEN t1.DSWX_VulnRating between 18 and 19 THEN 'Medium'
         WHEN t1.DSWX_VulnRating between 0 and 17 THEN 'Low'
         ELSE 'n/a'
      END as DSWX_VulnRatingDesc, 
      t1.DSWX_VulnRating, 
      CASE	
         WHEN t1.AgingDays > 14  THEN '14'
         WHEN t1.AgingDays > 30  THEN '30'
         WHEN t1.AgingDays > 60  THEN '60'
         WHEN t1.AgingDays  > 90 THEN '90'
         ELSE '0'
      END as Aging,
      t1.AgingDays,
      t1.DateDetected
   


from
(SELECT     h.DeviceName, 
		   h.IPAddress,
		   v.VulnID, 
		   v.VulnName, 
           v.VulnDescription, 
           h.RiskRatingAsset, 
           v.RiskRatingVuln,
           sc.Value AS VendorSeverityValue, 
           h.RiskRatingCounter AS RiskCounterMeasuresRating,
           h.RiskRatingAsset + sc.Value + v.RiskRatingVuln - h.RiskRatingCounter  AS DSWX_VulnRating, 
           v.Severity AS VendorSeverity, 
           v.DateDetected, 
           v.AgingDays, 
           v.ScanSource
FROM         dbo.SWRX_SeverityConversionTable  sc RIGHT OUTER JOIN  dbo.SWRX_MasterVulnerabilityList v  
					ON sc.Severity = v.Severity 
			 RIGHT OUTER JOIN  dbo.SWRX_MasterHost h
			  ON v.DeviceName = h.DeviceName 
WHERE     (NOT (v.VulnName LIKE 'CD-%'))
)t1

)t2

where t2.ScanSource <> 'Sherlock'
)t3

WHERE t3.DSWX_VulnRatingDesc = @Rating and t3.Aging = @Aging
	and t3.DateDetected < @Today AND
    t3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
    (DATEDIFF(day, @Today, t3.DateDetected) % @Nth) = 0
group by t3.DateDetected
) as TotalsForAging



end

Open in new window

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
G Trurab Khan

8/22/2022 - Mon
Rimvis

Hi robbhill,


Somehow I need this to pivot on Date....so it would look like this.

date           total1   total2

5/12/2011  1          1
6/9/2011    2           1
6/23/2011  17         null or 0
6/30/2011  137       null or 0

Please clarify this. Because you example is not a PIVOT on date column. Looks more like LEFT OUTER JOIN:

SELECT table1.date, table1.total1, table2.total2
FROM table1 LEFT OUTER JOIN table2 ON table1.date=table2.date
ASKER CERTIFIED SOLUTION
G Trurab Khan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Your help has saved me hundreds of hours of internet surfing.
fblack61