Union Query

karinos57
karinos57 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer
Commented:
you can just use the in to define your subset.  See below:

(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()) in(1,2,3)
order by date asc'))a
group by Date, MachineType, Node
order by date asc
AneeshDatabase Consultant
Top Expert 2009
Commented:
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
UNION ALL

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
UNION ALL
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

Author

Commented:
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
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

You can try using correlated subquery.   I provided a possible approach below.   Note that this was not tested and this may have some syntax errors.  I hope this helps.

SELECT Date, MachineType, Node,

(SELECT AVG(Availabity) 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) A1
WHERE A1.Date = MAIN.Date AND A1.MachineType = MAIN.MachineType AND A1.Node = MAIN.Node)  As AvgAvail_First,

(SELECT AVG(Availabity) 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) A2
WHERE A2.Date = MAIN.Date AND A2.MachineType = MAIN.MachineType AND A2.Node = MAIN.Node)  As AvgAvail_Second,

(SELECT AVG(Availabity) 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) A3
WHERE A3.Date = MAIN.Date AND A3.MachineType = MAIN.MachineType AND A3.Node = MAIN.Node)  As AvgAvail_Third,

FROM

(select Date, MachineType, Node, Availability
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  
order by date asc')) a
group by Date, MachineType, Node) MAIN

GROUP BY MAIN.Date, MAIN.MachineType, MAIN.Node
I don't think you need to use UNION there, try this:


select 	Date, 
	MachineType, 
	Node, 
	sum(case when Avail = 1 then Availability else 0 end) * 1.0 / sum(case when Avail = 1 then 1 else 0 end) as AvgAvail_Third, 
	sum(case when Avail = 2 then Availability else 0 end) * 1.0 / sum(case when Avail = 2 then 1 else 0 end)  as AvgAvail_Second, 
	sum(case when Avail = 3 then Availability else 0 end) * 1.0 / sum(case when Avail = 3 then 1 else 0 end) 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,
			datediff(dd,[DateTime],getdate()) as avail
		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()) between 1 and 3'
		)
) a

Open in new window


If you still want to go with UNION then

select Date, MachineType, Node, max(AvgAvail_First) as AvgAvail_First, max(AvgAvail_Second) as AvgAvail_second, max(AvgAvail_Third) as AvgAvail_Third
from (
select Date, MachineType, Node, Avg(Availability)as AvgAvail_First, 0 as AvgAvail_second, 0 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()) = 3'
	)
) a
group by Date, MachineType, Node

union all

--2nd query
select Date, MachineType, Node, 0, Avg(Availability)as AvgAvail_Second, 0
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')
) b
group by Date, MachineType, Node

union all

--3rd Query
select Date, MachineType, Node, 0, 0, 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')
) c
group by Date, MachineType, Node
) t1
order by date asc

Open in new window

Author

Commented:
tx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial