Solved

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

Posted on 2013-05-20
8
312 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

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.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

13 Experts available now in Live!

Get 1:1 Help Now