Solved

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

Posted on 2013-05-20
8
314 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
  • 4
  • 4
8 Comments
 
LVL 65

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 65

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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now