?
Solved

How do I get results from a query to be used in another query?

Posted on 2013-05-20
8
Medium Priority
?
322 Views
Last Modified: 2013-05-23
I have the following query which collects the latest volumes that exceed a threshold. It works great but now I need to also have it join another table to show when it first alerted.

The current query looks like:

;with cte as (
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize
)
select a.* from cte a
where a.datetime = (select max(datetime) from cte where nodeid=a.nodeid and volumeid = a.volumeid) order by Caption

I need it to now join the results from this query against the ALERTLOG table on the caption field from the above query on the ObjectName in the AlertLog table.

I have tried to join them but keep getting an error becuase the orginal query has a ";with cte' in it.

Ideas? Thanks!
0
Comment
Question by:edrz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39180979
Using a CTE means that the With ( ) query is only available for the query immediately beneath it.

If you wish to use that query in multiple places, I recommend creating a temp table, populating it, then referring to the temp table multiple times...

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp

CREATE TABLE #tmp (noteid as int, caption as varchar(50), etc int, etc bit)

-- Populate the temp table
INSERT INTO #tmp (noteid, caption, etc, etc)
{your above SELECT query goes here}

Then for the rest of the SP you can use the temp table #tmp any way you wish.
0
 

Author Comment

by:edrz01
ID: 39181112
Ok, so if I recode it as something like this?

Use NetPerfMon
drop table SLAC52
create table SLAC52
(NodeID integer,
Caption varchar(50),
VolumeID integer,
VolumeDescription varchar(100),
DateTime datetime,
AverageUtilization Integer)
GO
Insert SLAC52
(NodeID, Caption, VolumeID, VolumeDescription, DateTime, AverageUtilization)

select * From
(
;with cte as
(
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize
)
select a.* from cte a
where a.datetime = (select max(datetime) from cte where nodeid=a.nodeid and volumeid = a.volumeid) order by Caption
) SubQuery
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39181199
Temp tables are prefixed with pound signs (#), so if you use SLAC52 as the table name then would be a physical table.  Personal preference.

Not abundantly sure how you're using this, but give this a whirl.
Below this block you can use SLAC52 however you wish.

Use NetPerfMon
GO

IF EXISTS ( SELECT name FROM sys.tables where name='SLAC52') 
	DROP TABLE SLAC52
GO

create table SLAC52 
	(NodeID integer,
	Caption varchar(50),
	VolumeID integer,
	VolumeDescription varchar(100),
	DateTime datetime,
	AverageUtilization Integer)
GO

Insert SLAC52 (NodeID, Caption, VolumeID, VolumeDescription, DateTime, AverageUtilization)
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:edrz01
ID: 39181271
Ok, let me provide some data from the queries:

From the query:
Use NetPerfMon
GO

IF EXISTS ( SELECT name FROM sys.tables where name='SLAC52')
      DROP TABLE SLAC52
GO

create table SLAC52
      (NodeID integer,
      Caption varchar(50),
      VolumeID integer,
      VolumeDescription varchar(100),
      DateTime datetime,
      AverageUtilization Integer)
GO

Insert SLAC52 (NodeID, Caption, VolumeID, VolumeDescription, DateTime, AverageUtilization)
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize
----
Results look like:
2      ABCD      1212      C:\ Label:  2013-05-20 12:40:46.507      69
1422      C4V      1659      K:\ Label:SG4 DB  2013-05-20 12:38:12.330      70
1419      CS11      1680      F:\ Label:LS  2013-05-20 12:42:36.357      66
14      H18      252      C:\ Label:  2013-05-20 12:39:52.207      67
664      H20      1136      C:\ Label:  2013-05-20 12:39:57.207      67
664      H20      1137      F:\ Label:Data  2013-05-20 12:39:57.207      68
1377      H21      1998      C:\ Label:  2013-05-20 12:40:40.587      74
665      H22      1135      F:\ Label:Data  2013-05-20 12:42:29.230      73

When you remove the ";with cte as (" portion it looks liek ti shows all of ht eresulting data:

2      ABCD      1212      C:\ Label:  2013-05-15 10:24:01.847      69
2      ABCD      1212      C:\ Label:  2013-05-15 14:54:02.100      69
2      ABCD      1212      C:\ Label:  2013-05-15 20:39:21.540      69
2      ABCD      1212      C:\ Label:  2013-05-16 01:54:21.897      69
2      ABCD      1212      C:\ Label:  2013-05-16 02:54:22.107      69
2      ABCD      1212      C:\ Label:  2013-05-16 04:09:22.173      69
2      ABCD      1212      C:\ Label:  2013-05-16 04:39:22.510      69
2      ABCD      1212      C:\ Label:  2013-05-16 06:39:22.960      69
2      ABCD      1212      C:\ Label:  2013-05-16 15:10:04.777      69
2      ABCD      1212      C:\ Label:  2013-05-16 19:10:04.870      69
2      ABCD      1212      C:\ Label:  2013-05-16 22:10:05.060      69
2      ABCD      1212      C:\ Label:  2013-05-17 02:25:05.617      69
2      ABCD      1212      C:\ Label:  2013-05-17 03:25:06.170      69
2      ABCD      1212      C:\ Label:  2013-05-17 07:10:10.963      69
2      ABCD      1212      C:\ Label:  2013-05-17 11:40:16.377      69
2      ABCD      1212      C:\ Label:  2013-05-17 13:40:17.527      69
2      ABCD      1212      C:\ Label:  2013-05-17 14:55:19.573      69
2      ABCD      1212      C:\ Label:  2013-05-17 15:10:19.577      69
2      ABCD      1212      C:\ Label:  2013-05-17 17:55:21.843      69
2      ABCD      1212      C:\ Label:  2013-05-17 18:55:21.857      69
14      H18      252      C:\ Label:  2013-05-15 02:08:20.003      67
14      H18      252      C:\ Label:  2013-05-15 07:53:20.450      67
14      H18      252      C:\ Label:  2013-05-15 08:08:20.463      67
14      H18      252      C:\ Label:  2013-05-15 08:38:20.490      67
14      H18      252      C:\ Label:  2013-05-15 10:08:20.757      67
14      H18      252      C:\ Label:  2013-05-15 13:08:21.117      67
14      H18      252      C:\ Label:  2013-05-15 15:23:21.233      67
14      H18      252      C:\ Label:  2013-05-15 18:38:40.260      67
14      H18      252      C:\ Label:  2013-05-15 18:53:40.273      67
14      H18      252      C:\ Label:  2013-05-16 00:38:40.780      67
and on

The first query is supposed to pull the last value of volume usage using the cte
0
 

Author Comment

by:edrz01
ID: 39181667
Ok, making slow progress - but progress.

I recoded the query to this:
Use NetPerfMon
GO
IF OBJECT_ID('tempdb..#SLAC52') IS NOT NULL
   DROP TABLE #SLAC52
go
create table #SLAC52
(NodeID integer,
Caption varchar(50),
VolumeID integer,
VolumeDescription varchar(100),
DateTime datetime,
AverageUtilization Integer)
GO

;with cte as
(
select d.nodeid,n.Caption,d.volumeid, v.VolumeDescription,d.Datetime, round(AVG(D.PercentDiskUsed),0) as [AverageUtilization] from VolumeUsage_Detail D join dbo.Volumes V on D.nodeid = V.nodeid and d.volumeid = v.volumeid join dbo.nodes n on d.nodeid=n.nodeid where (n.SLA = '51' AND V.VolumeType like '%fixed%'
AND d.Datetime >= Dateadd(dd, -5, datediff(dd,0,getdate())) ) and D.PercentDiskUsed > 65 group by d.nodeid,n.caption,d.volumeid, v.VolumeDescription,d.datetime, d.disksize
)
Insert #SLAC52
(NodeID, Caption, VolumeID, VolumeDescription, DateTime, AverageUtilization)

select a.* from cte a
where a.datetime = (select max(datetime) from cte where nodeid=a.nodeid and volumeid = a.volumeid)
order by Caption
-----

So now I have data going into the temp table and can now do a join on that to get the other infor from the AlertLog. I will post the final version once I get it all together.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39181674
>create table #SLAC52
Once this executes you'll have an in-memory table named #SLAC52, with no rows.

>Insert #SLAC52
Once this executes that table is populated, enabling SQL like below...

select a.* from #SLAC52 a
where a.datetime = (select max(datetime) from cte where nodeid=a.nodeid and volumeid = a.volumeid)
order by Caption
0
 

Author Closing Comment

by:edrz01
ID: 39191851
Excellent solution! Directed me where I needed to look.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39192076
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 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