April 2, 2009 at 8:53 am
Using SQL Server 2005, what is the best way to render this query to use a dynamic date range?
I am trying to make a kludgey query (below) to render a similar result with data from only the most recent five weeks, using more of a dynamic code.
The query is based on an adaptation of David Byrd’s wonderful SQL Server monitoring application.
I have in mind something like pass in a parameter of @StartDate = getdate()-34 to fix the range of the result set, and then somehow loop through the data to find the values for each week.
SELECT
Server
, InstanceName
, Drive = upper(cast(left(FILENAME, 1) AS CHAR(1)))
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 1 THEN [Size(MB)] END) AS 'WEEK 01'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 2 THEN [Size(MB)] END) AS 'WEEK 02'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 3 THEN [Size(MB)] END) AS 'WEEK 03'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 4 THEN [Size(MB)] END) AS 'WEEK 04'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 5 THEN [Size(MB)] END) AS 'WEEK 05'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 6 THEN [Size(MB)] END) AS 'WEEK 06'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 7 THEN [Size(MB)] END) AS 'WEEK 07'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 8 THEN [Size(MB)] END) AS 'WEEK 08'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 9 THEN [Size(MB)] END) AS 'WEEK 09'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 10 THEN [Size(MB)] END) AS 'WEEK 10'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 11 THEN [Size(MB)] END) AS 'WEEK 11'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 12 THEN [Size(MB)] END) AS 'WEEK 12'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 13 THEN [Size(MB)] END) AS 'WEEK 13'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 14 THEN [Size(MB)] END) AS 'WEEK 14'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 15 THEN [Size(MB)] END) AS 'WEEK 15'
, SUM(CASE WHEN DATEPART(WK,DateChecked) = 16 THEN [Size(MB)] END) AS 'WEEK 16'
FROM Database_Volume
WHERE Server = 'MyServer'
GROUP BY Server, InstanceName, LEFT(FILENAME, 1)
Create a sample table and populate with sample data using the following:
CREATE TABLE [dbo].[Database_Volume](
[Server] [nvarchar](128) NOT NULL,
[InstanceName] [nvarchar](128) NULL,
[Filename] [nvarchar](260) NOT NULL,
[Size(MB)] [real] NOT NULL,
[DateChecked] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '11871.38', '2009-01-02 15:19:11.090')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '301', '2009-01-02 15:19:11.090')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '11763.94', '2009-01-09 07:03:17.493')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '364.0625', '2009-01-09 07:03:17.493')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '11673.5', '2009-01-16 07:02:49.933')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '314.8125', '2009-01-16 07:02:49.933')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '11174.5', '2009-01-23 07:02:35.703')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '372.125', '2009-01-23 07:02:35.703')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '11790.88', '2009-01-30 07:02:52.743')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '372.5625', '2009-01-30 07:02:52.743')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '12598.94', '2009-02-06 09:02:56.890')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '337.5625', '2009-02-06 09:02:56.890')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '12970.63', '2009-02-13 09:03:01.623')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '373.3125', '2009-02-13 09:03:01.623')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '13454.94', '2009-02-20 09:03:12.490')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '400.625', '2009-02-20 09:03:12.490')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '13901', '2009-02-27 09:03:39.520')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '410.1875', '2009-02-27 09:03:39.520')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '14011.75', '2009-03-06 09:03:00.983')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '409.625', '2009-03-06 09:03:00.983')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '14363.38', '2009-03-13 09:03:09.007')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '390.9375', '2009-03-13 09:03:09.007')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '13677.88', '2009-03-20 09:03:13.850')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '346.875', '2009-03-20 09:03:13.850')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'D:\Data\DataFile.MDF', '12799.38', '2009-03-27 09:04:01.910')
INSERT INTO dbo.Database_Volume (Server, InstanceName, Filename, [Size(MB)], DateChecked) VALUES ('MyServer', 'Default', 'T:\Log\LogFile.LDF', '400.75', '2009-03-27 09:04:01.910')
April 2, 2009 at 9:11 am
If you have Tally or Numbers table then you can loop through them to form a dynamic PIVOT clause (or SQL) and execute it using EXECUTE or sp_executesql statements.
--Ramesh
April 3, 2009 at 7:28 am
Ramesh (4/2/2009)
If you have Tally or Numbers table then you can loop through them to form a dynamic PIVOT clause (or SQL) and execute it using EXECUTE or sp_executesql statements.
What do you mean by a Tally or Numbers table?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply