December 7, 2009 at 6:45 am
Hi ,I have two queries ,i need to combine two of them and get the results
The below is the queries
select avg(Cast([["\\GBEDC-APPSP01\Memory\Pages/sec"]]] as float)) AS MemoryPages,
avg(Cast([["\\GBEDC-APPSP01\Memory\Available MBytes"]]] as INT)) AS MemoryAvailableMBytes ,
avg(Cast([["\\GBEDC-APPSP01\Processor(_Total)\% Processor Time"]]] as float)) AS ProcessorTime,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime,
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P01_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
select avg(Cast(["\\GBEDC-APPSP02\Memory\Pages/sec"] as float)) AS MemoryPages_1,
avg(Cast(["\\GBEDC-APPSP02\Memory\Available MBytes"] as INT)) AS MemoryAvailableMBytes_1 ,
avg(Cast(["\\GBEDC-APPSP02\Processor(_Total)\% Processor Time"] as float)) AS ProcessorTime_1,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P02_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
But i need the result in the this format
MemoryPages MemoryAvailableMbytes ProcessorTime DateTime MemoryPages_1
Totally all the columns need to be displayed in the table.I dont what to use Union Function as it gives the data in only 4 columns.
Thanx In Advanced
Jaya
December 7, 2009 at 7:14 am
It looks like you want to do an JOIN between the two queries, or Join the underlying tables and query them both.
To do this you need to provide the column that contains the data that defines the relationship between the two tables usually a PK->FK relationship, eg. ProcessorID or similar
December 7, 2009 at 7:45 am
How many readings do you have per day?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2009 at 8:46 am
there is no any PK or Fk relationship between this two tables ,or any column been identical,
thanx
Jaya
December 7, 2009 at 8:50 am
If there are no relationship then how do you want to combine them?
Post some sample data if you want some more specific help
December 7, 2009 at 9:01 am
does this work? if your SQLs return just one line, this look good, otherwise it'd do a cartesian join unless you add a where statement.
SELECT
X.MemoryPages, X.MemoryAvailableMbytes, X.ProcessorTime, X.[DateTime], Y.MemoryPages_1
FROM
(
select
avg(Cast([["\\GBEDC-APPSP01\Memory\Pages/sec"]]] as float)) AS MemoryPages,
avg(Cast([["\\GBEDC-APPSP01\Memory\Available MBytes"]]] as INT)) AS MemoryAvailableMBytes ,
avg(Cast([["\\GBEDC-APPSP01\Processor(_Total)\% Processor Time"]]] as float)) AS ProcessorTime,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P01_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
) X,
(
select
avg(Cast(["\\GBEDC-APPSP02\Memory\Pages/sec"] as float)) AS MemoryPages_1,
avg(Cast(["\\GBEDC-APPSP02\Memory\Available MBytes"] as INT)) AS MemoryAvailableMBytes_1 ,
avg(Cast(["\\GBEDC-APPSP02\Processor(_Total)\% Processor Time"] as float)) AS ProcessorTime_1,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P02_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
) Y
Lowell
December 7, 2009 at 9:30 am
If i run your quey ,i get a constant values for the column MemoryPages_1
say for any dateTime ,the column values seems to be the same..
December 7, 2009 at 9:38 am
jprabha.d (12/7/2009)
If i run your quey ,i get a constant values for the column MemoryPages_1say for any dateTime ,the column values seems to be the same..
[MemoryPages MemoryAvailableBytes ProcessorTIme Datetime MemoryPages_1
9.948098824 1538 1.228312605 11/25/2009 00 7.122892628
3.002041067 1723 0.825656218 11/25/2009 01 7.122892628
3.064543224 2554 0.881698261 11/25/2009 02 7.122892628
4.988632522 2615 0.707224869 11/25/2009 03 7.122892628
8.236930287 2505 1.338280872 11/25/2009 04 7.122892628
3.452333892 2422 0.891138064 11/25/2009 05 7.122892628
What is the name of your 5th column, reading from left to right?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2009 at 9:43 am
if the first query returns 6 rows, and the second returns 1, that is the results of the cartesian join we warned you about.
you have to determine what joins the two tables together to get any sensible data our of combining the two queries. no join would give you the ugly data you posted, where it doesn't seem to help with any analysis.
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply