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

0
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

0
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  
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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]

0
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

0
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

0

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

that's why a full list of all month as [c] is needed
0
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?
0
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.  
0
jclemoAuthor Commented:
If only I could give you more than 500 point I would
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.