Avatar of karinos57
karinos57
Flag for Afghanistan asked on

Union Query

Hi,
How can I use union query or any other method just combine the results from these 3 queries in one output result?  I basically want the output to be like this:

Date       MachineType       Node        AvgAvail_First         AvgAvail_Second                  AvgAvail_Third


here is my qry:
-- 1st Query
select Date, MachineType, Node, Avg(Availability)as AvgAvail_First
from

(select * from openquery(MyLink,
'SELECT convert (varchar, datetime, 101)as Date,
n.MachineType,
r.Availability / 100 AS AvgOfAvailability,
r.Availability,
n.Caption AS Node
FROM G3SQL1.perf.dbo.Nodes n INNER JOIN
G3SQL1.netperfmon.dbo.ResponseTime r ON n.NodeID = r.NodeID
and n.machinetype not like ''Windows%''
and MachineType is not null  
and datediff(dd,[DateTime],getdate()) = 3
order by date asc'))a
group by Date, MachineType, Node
order by date asc



--2nd Query
select Date, MachineType, Node, Avg(Availability)as AvgAvail_Second
from

(select * from openquery(MyLink,
'SELECT convert (varchar, datetime, 101)as Date,
n.MachineType,
r.Availability / 100 AS AvgOfAvailability,
r.Availability,
n.Caption AS Node
FROM G3SQL1.perf.dbo.Nodes n INNER JOIN
G3SQL1.netperfmon.dbo.ResponseTime r ON n.NodeID = r.NodeID
and n.machinetype not like ''Windows%''
and MachineType is not null  
and datediff(dd,[DateTime],getdate()) = 2
order by date asc'))a
group by Date, MachineType, Node
order by date asc


--3rd Query
select Date, MachineType, Node, Avg(Availability)as AvgAvail_Third
from

(select * from openquery(MyLink,
'SELECT convert (varchar, datetime, 101)as Date,
n.MachineType,
r.Availability / 100 AS AvgOfAvailability,
r.Availability,
n.Caption AS Node
FROM G3SQL1.perf.dbo.Nodes n INNER JOIN
G3SQL1.netperfmon.dbo.ResponseTime r ON n.NodeID = r.NodeID
and n.machinetype not like ''Windows%''
and MachineType is not null  
and datediff(dd,[DateTime],getdate()) = 1
order by date asc'))a
group by Date, MachineType, Node
order by date asc

thanks
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
karinos57

8/22/2022 - Mon
SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
karinos57

ASKER
thanks guys but both these results are not giving me what i need.   I would like to see these fields also in my final output
 AvgAvail_First
AvgAvail_Second
AvgAvail_Third

Also, specific Node might be missing from the data results when u run the first query but it might show up in the next day or when u run the 2nd query or the the third query so in that case we cannot run inner join query.  When i run the query, i would like to see the final output to have all these fields:
Date       MachineType       Node        AvgAvail_First         AvgAvail_Second                  AvgAvail_Third

thanks
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
karinos57

ASKER
tx
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck