Assistance with Query

Question

Assistance with Query

SQLSACT

SSC-Insane

Points: 21838
More actions
October 6, 2017 at 5:06 am

#339805

Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 1

SQLSACT - Friday, October 6, 2017 5:06 AM
Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks

SELECT file_id, name, type_desc, physical_name, (size*8)/1024 SizeInMB, max_size,getdate() into #Space FROM sys.database_files ;

First solve the problem then write the code !

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SQLSACT - Friday, October 6, 2017 5:06 AM
Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks

SELECT
[DBName],
[BFileName],
[FileType],
YearAndMonth,
[MaxMBUsed] = MAX([MBUsed]),
[MinMBUsed] = MIN([MBUsed])
FROM [dbo].[Space]
CROSS APPLY (SELECT YearAndMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,[DateUpdated]),0)) x
GROUP BY [DBName], [BFileName], [FileType], YearAndMonth
ORDER BY [DBName], [BFileName], [FileType], YearAndMonth

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

ChrisM@Work - Friday, October 6, 2017 6:51 AM
SQLSACT - Friday, October 6, 2017 5:06 AM
Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks
SELECT
[DBName],
[BFileName],
[FileType],
YearAndMonth,
[MaxMBUsed] = MAX([MBUsed]),
[MinMBUsed] = MIN([MBUsed])
FROM [dbo].[Space]
CROSS APPLY (SELECT YearAndMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,[DateUpdated]),0)) x
GROUP BY [DBName], [BFileName], [FileType], YearAndMonth
ORDER BY [DBName], [BFileName], [FileType], YearAndMonth

Thank you - This helps
So most of the databases have multiple files.
How would I modify this to get a result that shows the total growth of each database (total growth of MBUsed) month to month?

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SQLSACT - Friday, October 6, 2017 7:16 AM
ChrisM@Work - Friday, October 6, 2017 6:51 AM
SQLSACT - Friday, October 6, 2017 5:06 AM
Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks
SELECT
[DBName],
[BFileName],
[FileType],
YearAndMonth,
[MaxMBUsed] = MAX([MBUsed]),
[MinMBUsed] = MIN([MBUsed])
FROM [dbo].[Space]
CROSS APPLY (SELECT YearAndMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,[DateUpdated]),0)) x
GROUP BY [DBName], [BFileName], [FileType], YearAndMonth
ORDER BY [DBName], [BFileName], [FileType], YearAndMonth
Thank you - This helps
So most of the databases have multiple files.
How would I modify this to get a result that shows the total growth of each database (total growth of MBUsed) month to month?
Thanks

Write a query which aggregates MBused by database and date, and use that as the input for the query I posted.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

ChrisM@Work - Friday, October 6, 2017 7:21 AM
SQLSACT - Friday, October 6, 2017 7:16 AM
ChrisM@Work - Friday, October 6, 2017 6:51 AM
SQLSACT - Friday, October 6, 2017 5:06 AM
Hello All
I have the following table...
CREATE TABLE [dbo].[Space]( [DBName] [varchar](128) NOT NULL, [BFileName] [varchar](128) NOT NULL, [MBUsed] [numeric](20, 3) NOT NULL, [FileType] [varchar](5) NOT NULL, [DateUpdated] [smalldatetime] NOT NULL ) ON
This table is updated daily with the relevant information.
I would like to produce a report which shows the monthly usage growth for each database.
I'm struggling to put a query together to achieve this.
Any assistance would be really appreciated.
Thanks
SELECT
[DBName],
[BFileName],
[FileType],
YearAndMonth,
[MaxMBUsed] = MAX([MBUsed]),
[MinMBUsed] = MIN([MBUsed])
FROM [dbo].[Space]
CROSS APPLY (SELECT YearAndMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,[DateUpdated]),0)) x
GROUP BY [DBName], [BFileName], [FileType], YearAndMonth
ORDER BY [DBName], [BFileName], [FileType], YearAndMonth
Thank you - This helps
So most of the databases have multiple files.
How would I modify this to get a result that shows the total growth of each database (total growth of MBUsed) month to month?
Thanks
Write a query which aggregates MBused by database and date, and use that as the input for the query I posted.

Thanks
I think I'm there.....

select s.DBName, YearAndMonth, sum(s.MaxMBUsed) from (

SELECT [DBName], [BFileName], [FileType], YearAndMonth, [MaxMBUsed] = MAX([UsedMb]), [MinMBUsed] = MIN([UsedMb]) FROM [dbo].[Space] CROSS APPLY (SELECT YearAndMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,[DateUpdated]),0)) x GROUP BY [DBName], [BFileName], [FileType], YearAndMonth --ORDER BY [DBName], [BFileName], [FileType], YearAndMonth ) s

group by DBName, YearAndMonth order by DBName, YearAndMonth