Join the results of two queries on one record

I have these two queries which return the expected results.. how do I get them into one record grouped on the MM/YYYY which I have defined?

SELECT CAST(DATEPART(month,ScanStartDate) AS NVARCHAR(50)) + '/' + CAST(DATEPART(year,ScanStartDate) AS NVARCHAR(50))[Month]
,COUNT(VulnInstance) AS [Open]
FROM Vulnerabilities
GROUP BY DATEPART(month,ScanStartDate), DATEPART(year,ScanStartDate)
ORDER BY DATEPART(month,ScanStartDate),DATEPART(year,ScanStartDate)

SELECT CAST(DATEPART(month,Remediation_Date) AS NVARCHAR(50)) + '/' + CAST(DATEPART(year,Remediation_Date) AS NVARCHAR(50))[Month]
,COUNT(VulnInstance) AS [Closed]
FROM Vulnerabilities
GROUP BY DATEPART(month,Remediation_Date), DATEPART(year,Remediation_Date)
ORDER BY DATEPART(month,Remediation_Date),DATEPART(year,Remediation_Date)

I want the results to be as follows:

Month      |   Open   |   Closed   |  Residual
09/2008   |   200     |    20         |    180
10/2008   |   14       |    10         |    184
11/2008   |    10      |    194       |      0

I have the open and close piece.. but I can't figure out how to get them on the same line to do the calculation, thanks
jclemoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jamesguCommented:
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

Open in new window

jamesguCommented:
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

Open in new window

jclemoAuthor Commented:
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  
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

jclemoAuthor Commented:
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]

jamesguCommented:
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)

Open in new window

jamesguCommented:
use isnull(col, 0)
SELECT DATEPART(month,ScanStartDate),isnull([a].[Open], 0) as [Open], isnull([b].[Closed], 0) as [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)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesguCommented:
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
jclemoAuthor Commented:
Thanks again jamesgu, do say that because I am using the "ScanStartDate" to generate the dates in the top select query ???
jamesguCommented:
yes, you're about that,

that's why a full list of all month as [c] is needed
jclemoAuthor Commented:
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?
jclemoAuthor Commented:
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.  
jclemoAuthor Commented:
If only I could give you more than 500 point I would
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.