kodaksmile
MIS
I have 2 tables - one contains application information that has been pulled in from the extended properties and looks like:
Application information Table:
DBName Application Description
test1 MS_Application Test Application
test1 MS_Description Used for testing
round2 MS_Application Round2 Application
round2 MS_Description used for round2 test
There will be 2 records for each database as above - all fields are varchar.
The second table contains the space information collected nightly for each db and has this structure:
CREATE TABLE [dbo].[dbaSpace](
[LNm] [char](40) NULL,
[Tot] [numeric](10, 4) NULL,
[Used] [numeric](10, 4) NULL,
[Prcnt] [numeric](10, 4) NULL,
[DB] [char](40) NULL,
[Typ] [char](5) NULL,
[ServerName] [varchar](50) NULL,
[EntryDt] [varchar](10) NULL
) ON [PRIMARY]
What I am trying to do is write a query that will pull the data from the description field into 2 different fields based on what is contained in the application column of table 1. (basically I need to see Test Application as the application name and Used for testing as the description)
Desired results:
Servername, dbname, application name, application description, totalAvail(MB), TotalUsed(MB), FreeSpace(MB), Date
I can easily get all but the application name and description. The problem comes in because the real application name and the application description are both located in the description field of the application information table.
I tried:
select ServerName,db as DatabaseName,
(select description from applicationinformation where Application='MS_Application') as Application,
(select description from applicationinformation where application='MS_Description') as description,
sum(tot) as 'TotalAvail(MB)', sum(used) as 'TotalUsed(MB)', sum(tot)-sum(used) as 'FreeSpace(MB)',
entrydt as Date from dbaspace, ApplicationInformation where dbaspace.db in (select distinct applicationinformation.dbname
from applicationinformation)
and entrydt='04/01/2010'
group by ServerName,db, entrydt, application , description
order by entrydt
but it errors out becuase there are multiple rows returned.
any suggestions?
Application information Table:
DBName Application Description
test1 MS_Application Test Application
test1 MS_Description Used for testing
round2 MS_Application Round2 Application
round2 MS_Description used for round2 test
There will be 2 records for each database as above - all fields are varchar.
The second table contains the space information collected nightly for each db and has this structure:
CREATE TABLE [dbo].[dbaSpace](
[LNm] [char](40) NULL,
[Tot] [numeric](10, 4) NULL,
[Used] [numeric](10, 4) NULL,
[Prcnt] [numeric](10, 4) NULL,
[DB] [char](40) NULL,
[Typ] [char](5) NULL,
[ServerName] [varchar](50) NULL,
[EntryDt] [varchar](10) NULL
) ON [PRIMARY]
What I am trying to do is write a query that will pull the data from the description field into 2 different fields based on what is contained in the application column of table 1. (basically I need to see Test Application as the application name and Used for testing as the description)
Desired results:
Servername, dbname, application name, application description, totalAvail(MB), TotalUsed(MB), FreeSpace(MB), Date
I can easily get all but the application name and description. The problem comes in because the real application name and the application description are both located in the description field of the application information table.
I tried:
select ServerName,db as DatabaseName,
(select description from applicationinformation where Application='MS_Application') as Application,
(select description from applicationinformation where application='MS_Description') as description,
sum(tot) as 'TotalAvail(MB)', sum(used) as 'TotalUsed(MB)', sum(tot)-sum(used) as 'FreeSpace(MB)',
entrydt as Date from dbaspace, ApplicationInformation where dbaspace.db in (select distinct applicationinformation.dbname
from applicationinformation)
and entrydt='04/01/2010'
group by ServerName,db, entrydt, application , description
order by entrydt
but it errors out becuase there are multiple rows returned.
any suggestions?