Join the results of two queries on one record

try this

with t( monthh, openn, closee, residual)
as (
	select a.[Month], a.[Open], b.[Close], a.[Open] - b.[Close] as Residual
	from (
		SELECT convert(datetime, cast(datepart(year, Remediation_Date) as varchar) + cast(datepart(month, Remediation_Date) as varchar) + '01' ) [Month]
		,COUNT(VulnInstance) AS [Closed]
		FROM Vulnerabilities
		GROUP BY convert(datetime, cast(datepart(year, Remediation_Date) as varchar) + cast(datepart(month, Remediation_Date) as varchar) + '01' )
	) a
	, (
		SELECT convert(datetime, cast(datepart(year, ScanStartDate) as varchar) + cast(datepart(month, ScanStartDate) as varchar) + '01' ) [Month]
		,COUNT(VulnInstance) AS [Open]
		FROM Vulnerabilities
		GROUP BY convert(datetime, cast(datepart(year, ScanStartDate) as varchar) + cast(datepart(month, ScanStartDate) as varchar) + '01' )
	) b
	where a.[Month] = b.[Month]
)
select monthh, openn, closee, (select sum(residual) from t b where a.monthh => b.monthh) as residual
from t a

Select allOpen in new window

forgot to convert the datetime to string

with t( monthh, openn, closee, residual)
as (
        select a.[Month], a.[Open], b.[Close], a.[Open] - b.[Close] as Residual
        from (
                SELECT convert(datetime, cast(datepart(year, Remediation_Date) as varchar) + cast(datepart(month, Remediation_Date) as varchar) + '01' ) [Month]
                ,COUNT(VulnInstance) AS [Closed]
                FROM Vulnerabilities
                GROUP BY convert(datetime, cast(datepart(year, Remediation_Date) as varchar) + cast(datepart(month, Remediation_Date) as varchar) + '01' )
        ) a
        , (
                SELECT convert(datetime, cast(datepart(year, ScanStartDate) as varchar) + cast(datepart(month, ScanStartDate) as varchar) + '01' ) [Month]
                ,COUNT(VulnInstance) AS [Open]
                FROM Vulnerabilities
                GROUP BY convert(datetime, cast(datepart(year, ScanStartDate) as varchar) + cast(datepart(month, ScanStartDate) as varchar) + '01' )
        ) b
        where a.[Month] = b.[Month]
)
select CAST(DATEPART(month, monthh) AS NVARCHAR(50)) + '/' + CAST(DATEPART(year, monthh) AS NVARCHAR(50))
, openn
, closee
, (select sum(residual) from t b where a.monthh => b.monthh) as residual
from t a

Select allOpen in new window

thanks for the response jamesqu:

based on your response and some tinkering of course I ended up with the following query:

SELECT DATEPART(month,ScanStartDate),[a].[Open],[b].[Closed] FROM
(
SELECT DATEPART(month,ScanStartDate) AS [Date], Count(VulnInstance)
AS [Open]
FROM Vulnerabilities
WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW')
GROUP BY DATEPART(month,ScanStartDate)
) a,

(
SELECT DATEPART(month,Remediation_Date) AS [Date], Count(VulnInstance)
AS [Closed]
FROM Vulnerabilities
WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW')
GROUP BY DATEPART(month,Remediation_Date)
) b,

Vulnerabilities

WHERE a.Date = DATEPART(month,ScanStartDate)
AND b.Date = DATEPART(month,ScanStartDate)
GROUP BY DATEPART(month,ScanStartDate),[a].[Open],[b].[Closed]
ORDER BY DATEPART(month,ScanStartDate)

This seems to do what I want it to do but I find that BOTH OPEN and CLOSED must have values in them for the query to display a record.. for example.. if I have this data

Month   |   Open  |  Closed
3               10          0              
or this data:
Month   |   Open  |  Closed
3               0            24

I see no record for the month at all.. I'm pretty sure this has to do with my where statement at the bottom.. would you mind taking a look when you get a chance,, thanks in advance  

k.. got that fixed with the following query.. but now the fields have NULL in them.. I think the last piece of the puzzle is to make those zero.

SELECT DATEPART(month, Vulnerabilities_2.ScanStartDate) AS [Date], a.[Open], b.Closed FROM

(SELECT DATEPART(month, ScanStartDate) AS Date, COUNT(VulnInstance) AS [Open] FROM Vulnerabilities
WHERE(Addinfo >=(SELECT Threshold FROM CurrentThresholds WHERE (Rating = 'LOW')))
GROUP BY DATEPART(month, ScanStartDate)) AS a

LEFT OUTER JOIN

Vulnerabilities AS Vulnerabilities_2 ON a.Date = DATEPART(month, Vulnerabilities_2.ScanStartDate)

LEFT OUTER JOIN

(SELECT DATEPART(month, Remediation_Date) AS Date, COUNT(VulnInstance) AS Closed FROM Vulnerabilities
WHERE (Addinfo >=(SELECT Threshold FROM CurrentThresholds WHERE(Rating = 'LOW')))
GROUP BY DATEPART(month, Remediation_Date)) AS b

ON DATEPART(month, Vulnerabilities_2.ScanStartDate) = b.Date
GROUP BY DATEPART(month, Vulnerabilities_2.ScanStartDate), a.[Open], b.Closed
ORDER BY [Date]

you may need a derived table c

(
select DATEPART(month,Remediation_Date) AS [Date]
FROM Vulnerabilities
WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW')
GROUP BY DATEPART(month,Remediation_Date)
union
select DATEPART(month, ScanStartDate) AS [Date]
FROM Vulnerabilities
WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW')
GROUP BY DATEPART(month, ScanStartDate)
) c

SELECT DATEPART(month,ScanStartDate),[a].[Open],[b].[Closed] FROM
Vulnerabilities,
(
	select DATEPART(month,Remediation_Date) AS [Date] 
	FROM Vulnerabilities 
	WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW') 
	GROUP BY DATEPART(month,Remediation_Date)
	union
	select DATEPART(month, ScanStartDate) AS [Date] 
	FROM Vulnerabilities 
	WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW') 
	GROUP BY DATEPART(month, ScanStartDate)
) c 
left join
	(
	SELECT DATEPART(month,ScanStartDate) AS [Date], Count(VulnInstance) 
	AS [Open] 
	FROM Vulnerabilities 
	WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW') 
	GROUP BY DATEPART(month,ScanStartDate)
	) a on a.[Date] = c.[Date]
left join 
(
SELECT DATEPART(month,Remediation_Date) AS [Date], Count(VulnInstance) 
AS [Closed] 
FROM Vulnerabilities 
WHERE AddInfo >= (SELECT Threshold FROM CurrentThresholds WHERE Rating = 'LOW') 
GROUP BY DATEPART(month,Remediation_Date)
) b on b.[Date] = c.[Date] 
 
WHERE c.[Date] = DATEPART(month,ScanStartDate) 
GROUP BY DATEPART(month,ScanStartDate),[a].[Open],[b].[Closed]
ORDER BY DATEPART(month,ScanStartDate)

Select allOpen in new window

i have a feeling that your query missing one of the below case

Month   |   Open  |  Closed
3               10          0              
or this data:
Month   |   Open  |  Closed
3               0            24

seems it's  the second

Thanks again jamesgu, do say that because I am using the "ScanStartDate" to generate the dates in the top select query ???

yes, you're about that,

that's why a full list of all month as [c] is needed

ahh.. every record in my data does have a ScanStartDate value though.. it's a pre-populated field so it is not possible for it to be NULL.. perhaps my question was misleading when I said:

or this data:
Month   |   Open  |  Closed
3               0            24

when that scenario really can't exist.  Sorry for the confusion.. for the other question about substituting the 0 for the nulls should I open another question after I award the points for this one?

Oh.. I didn't see that you put a comment at the top of ID: 22936720 which actually answered my question.. thank you so much.. if only I could give you more than 500 point I would.  

If only I could give you more than 500 point I would