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)ASBEGINDECLARE @Nth int, @MaxGoBack int, @Today datetimeSET @Nth = 7SET @MaxGoBack = 90SET @Today = DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')select TotalsForRating.DateDetected, TotalsForRating.Total as Rating, TotalsForAging.total as totalfrom (SELECT t3.DateDetected, COUNT(t3.VulnName) AS Totalfrom(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.VendorSeverityValuefrom(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.DateDetectedfrom(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.ScanSourceFROM 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)t2where t2.ScanSource <> 'Sherlock')t3WHERE t3.DSWX_VulnRatingDesc = @Rating and t3.DateDetected < @Today AND t3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND (DATEDIFF(day, @Today, t3.DateDetected) % @Nth) = 0group by t3.DateDetected) as TotalsForRating,(SELECT t3.DateDetected, COUNT(t3.VulnName) AS Totalfrom(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.VendorSeverityValuefrom(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.DateDetectedfrom(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.ScanSourceFROM 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)t2where t2.ScanSource <> 'Sherlock')t3WHERE 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) = 0group by t3.DateDetected) as TotalsForAgingend
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