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
Medium Priority
198 Views
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
Question by:jclemo
• 6
• 6

LVL 9

Expert Comment

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
``````
0

LVL 9

Expert Comment

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
``````
0

Author Comment

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

Author Comment

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

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)
``````
0

LVL 9

Accepted Solution

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)
``````
0

LVL 9

Expert Comment

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

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

ID: 22936772

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

Author Comment

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

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

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

## Featured Post

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
Course of the Month10 days, 16 hours left to enroll