Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Join the results of two queries on one record

Posted on 2008-11-11
12
Medium Priority
?
198 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:jclemo
  • 6
  • 6
12 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22936300
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22936305
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
 

Author Comment

by:jclemo
ID: 22936640
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jclemo
ID: 22936707
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22936716
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
 
LVL 9

Accepted Solution

by:
jamesgu earned 2000 total points
ID: 22936720
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22936728
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
 

Author Comment

by:jclemo
ID: 22936752
Thanks again jamesgu, do say that because I am using the "ScanStartDate" to generate the dates in the top select query ???
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22936772
yes, you're about that,

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

Author Comment

by:jclemo
ID: 22936790
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
 

Author Comment

by:jclemo
ID: 22936857
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
 

Author Closing Comment

by:jclemo
ID: 31515802
If only I could give you more than 500 point I would
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question