June 15, 2016 at 6:10 am
Good day experts,
I have two sql quiries that i want to combine so that they can bring one result.Please help how can i do that.The quiries are as follows
A) select
a.SiteName,
a.Hostname,
a.FreeSpace,
a.FreeSpacePerc,
a.Color,
b.TotalMemory,
'ProLiant DL585 G7' as Model
from
(
SELECT
[SiteName],
[Hostname],
[SystemDriveName] as DriveName,
cast([SystemFreeSpace] as nvarchar(10)) + ' GB (' + [SystemDriveName] + ':)' as FreeSpace,
cast(([SystemFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [SystemDriveName] + ':)' as FreeSpacePerc,
case when [SystemFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
select * from
(
SELECT
[SiteName],
[Hostname],
[DataDriveName],
cast([DataFreeSpace] as nvarchar(10)) + ' GB (' + [DataDriveName] + ':)' as FreeSpace,
cast(([DataFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [DataDriveName] + ':)' as FreeSpacePerc,
case when [DataFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
SELECT
[SiteName],
[Hostname],
[BackupDriveName],
cast([BackupFreeSpace] as nvarchar(10)) + ' GB (' + [BackupDriveName] + ':)' as FreeSpace,
cast(([BackupFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [BackupDriveName] + ':)' as FreeSpacePerc,
case when [BackupFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
Union
SELECT
[SiteName],
[Hostname],
[Backup2DriveName],
cast([Backup2FreeSpace] as nvarchar(10)) + ' GB (' + [Backup2DriveName] + ':)' as FreeSpace,
cast(([Backup2FreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [Backup2DriveName] + ':)' as FreeSpacePerc,
case when [BackupFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
) as a
) as a
join
(
SELECT
[SiteName],
[Hostname],
[TotalMemory] + ' GB' as TotalMemory
FROM [FednaDBA].[dbo].[HostMemoryCPUStatus]
where
active = 1 and
Hostname like 'AC%'
) as b on a.SiteName = a.SiteName and a.Hostname = b.Hostname
order by
a.[SiteName], DriveName
B) SELECT
[SiteName],
[Hostname],
[SystemDriveName] as DriveName,
cast([SystemFreeSpace] as nvarchar(10)) + ' GB (' + [SystemDriveName] + ':)' as FreeSpace,
cast(([SystemFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [SystemDriveName] + ':)' as FreeSpacePerc,
cast([Memory] as nvarchar(10)) + ' GB ' as Memory,
case when [SystemFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color,
Createdon
FROM [FednaDBA].[dbo].[DriveStatus_Solarwinds]
where
--active = 1 and
Hostname like 'AC%'and Createdon >= DATEADD(day, DATEDIFF(day,0,GETDATE()),0)
Thank you in advance
June 15, 2016 at 6:52 am
You gave us the queries, but please also show what they return (for each one) and also what you desire the 'combined' version to return.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 15, 2016 at 7:37 am
You're cross posting on your own cross post. Please, all responses on this thread, not the other one.
The easiest way to start to combine two queries is to treat each as a table:
SELECT *
FROM (SELECT * FROM Query1) AS t1
JOIN (SELECT * FROM Query2) AS t2
ON t1.SomeValue = t2.SomeValue
WHERE...
That's the basics. It just requires that there be a column (or columns) between the first and second query that you can JOIN on. After changing them, you'll want to eliminate duplicate columns (if any) just for the sake of clarity (and, as written, the pseudo-code above will fail if there are duplicate columns between the queries).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2016 at 11:01 am
Grant Fritchey (6/15/2016)
You're cross posting on your own cross post. Please, all responses on this thread, not the other one.The easiest way to start to combine two queries is to treat each as a table:
SELECT *FROM (SELECT * FROM Query1) AS t1
JOIN (SELECT * FROM Query2) AS t2
ON t1.SomeValue = t2.SomeValue
WHERE...
That's the basics. It just requires that there be a column (or columns) between the first and second query that you can JOIN on. After changing them, you'll want to eliminate duplicate columns (if any) just for the sake of clarity (and, as written, the pseudo-code above will fail if there are duplicate columns between the queries).
Just to clarify even more, my experience has been such that if you just put the query into SSMS it will work with dupe columns. Its only when SQL itself has to make use of the result set column names that problems arise. Of course, I realise its probably not very useful info 🙂
June 15, 2016 at 12:59 pm
The queries will return information in the format below
eg Sitename = Kriel
HostName =ACZADRF
Freespace =62.34 GB(D)
FreeSpacePerc = 39%(D:)
Color = Black
TotalMemory = 64.GB
Model = Proliant DL585 G7
SiteName,HostName,Freespace,FreeSpacePerc ,Color ,TotalMemory ,Model
Kriel,ACZADRF,62.34 GB(D), 39%(D:), Black, 64.GB, Proliant DL585 G7
Isibonelo,ACZALCD,4902.19 GB(F),89% (F:),BLACK,160 GB,ProLiant DL585
Mafube,ACZAHEN,36.53 GB(C:),27.00%(C:),BLACK 160.00 GB ProLiant DL5
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply