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

Select allOpen in new window