March 23, 2017 at 12:49 am
ServerName DBName Total Disk Space LastInserted Rank1
ServerA DBA 99999 2016-11-21 02:18:54.993 1
ServerA DBA 999999 2017-03-12 06:00:12.580 10
ServerB DBZ 8888 2016-11-21 02:18:55.003 1
ServerB DBZ 888888 2017-03-12 06:00:12.590 33
ServerC DBC 77777 2016-11-21 02:18:55.017 1
ServerC DBC 7777777 2017-03-12 06:00:12.590 100
ServerD DBD 500 2016-11-21 02:18:55.023 1
ServerD DBD 50000 2017-03-12 06:00:12.590 66
ServerE DBE 666666 2016-11-21 02:18:55.027 1
ServerE DBE 66666666 2017-03-12 06:00:12.590 17
I have this above view which carries data from across server with DB size growth over a period of time.
The above view gives the max and min Db sizes.
The result should be the difference between DB sizes based on max and min ranks for the respective DB's
So result for above table should be
ServerName DBName Total Disk Space Growth period Difference days in Start/End Dates in days
ServerA DBA 900000 2016-11-21 02:18:54.993 2017-03-12 06:00:12.580 81
ServerB DBZ 880000 2016-11-21 02:18:55.003 2017-03-12 06:00:12.590 81
ServerC DBC 7700000 2016-11-21 02:18:55.017 2017-03-12 06:00:12.590 81
ServerD DBD 49500 2016-11-21 02:18:55.023 2017-03-12 06:00:12.590 81
ServerE DBE 66000000 2016-11-21 02:18:55.027 2017-03-12 06:00:12.590 81
Thanks
March 23, 2017 at 3:25 am
If the following assumptions are true, the code below should help
1. You have only 2 rows per ServerName, DBName combination
2. Rank1 = 1 always for the record with minimum DB size
DECLARE @databases TABLE
(
ServerName VARCHAR(50),
DBName VARCHAR(50),
TotalDiskSpace NUMERIC(18,2),
LastInserted DATETIME,
Rank1 INT
)
INSERT @databases( ServerName, DBName, TotalDiskSpace, LastInserted, Rank1 )
SELECT 'ServerA', 'DBA', 99999, '2016-11-21 02:18:54.993', 1 UNION ALL
SELECT 'ServerA', 'DBA', 999999, '2017-03-12 06:00:12.580', 10 UNION ALL
SELECT 'ServerB', 'DBZ', 8888, '2016-11-21 02:18:55.003', 1 UNION ALL
SELECT 'ServerB', 'DBZ', 888888, '2017-03-12 06:00:12.590', 3 UNION ALL
SELECT 'ServerC', 'DBC', 77777, '2016-11-21 02:18:55.017', 1 UNION ALL
SELECT 'ServerC', 'DBC', 7777777, '2017-03-12 06:00:12.590', 100 UNION ALL
SELECT 'ServerD', 'DBD', 500, '2016-11-21 02:18:55.023', 1 UNION ALL
SELECT 'ServerD', 'DBD', 50000, '2017-03-12 06:00:12.590', 66 UNION ALL
SELECT 'ServerE', 'DBE', 666666, '2016-11-21 02:18:55.027', 1 UNION ALL
SELECT 'ServerE', 'DBE', 66666666, '2017-03-12 06:00:12.590', 17
SELECT *
FROM (
SELECT ServerName, DBName, LEAD( TotalDiskSpace ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) - TotalDiskSpace AS Growth,
LastInserted AS StartDate, LEAD( LastInserted ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) AS EndDate,
DATEDIFF( DAY, LastInserted, LEAD( LastInserted ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) ) AS DifferenceDays, Rank1
FROM @databases
) AS T
WHERE Rank1 = 1
For faster and tested solutions, please provide sample data and DDL in future as I had done for this post
You can also go through the link in my signature for additional details
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 23, 2017 at 3:35 am
Because of the wording the OP used, I want to double check; You say that the data set brings back data over a period of time. In your example you only supply 2 values for each server. Will this view of yours always bring back 2 rows? If not, then Kingston's code isn't going to work for your needs (sorry Kingston, but the OP's wording seems to imply what their data does not).
If this is the case though, when you do supply your consumable data, supply it so that it's representative of all your scenarios. If the amount of lines of data returned per server is variable, then do that in your sample data. if you only supply two lines for every server, we're going to assume that your data is representative and will only ever return two rows per server.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 23, 2017 at 8:41 am
Kingston Dhasian - Thursday, March 23, 2017 3:25 AMIf the following assumptions are true, the code below should help
1. You have only 2 rows per ServerName, DBName combination
2. Rank1 = 1 always for the record with minimum DB sizeDECLARE @databases TABLE
(
ServerName VARCHAR(50),
DBName VARCHAR(50),
TotalDiskSpace NUMERIC(18,2),
LastInserted DATETIME,
Rank1 INT
)INSERT @databases( ServerName, DBName, TotalDiskSpace, LastInserted, Rank1 )
SELECT 'ServerA', 'DBA', 99999, '2016-11-21 02:18:54.993', 1 UNION ALL
SELECT 'ServerA', 'DBA', 999999, '2017-03-12 06:00:12.580', 10 UNION ALL
SELECT 'ServerB', 'DBZ', 8888, '2016-11-21 02:18:55.003', 1 UNION ALL
SELECT 'ServerB', 'DBZ', 888888, '2017-03-12 06:00:12.590', 3 UNION ALL
SELECT 'ServerC', 'DBC', 77777, '2016-11-21 02:18:55.017', 1 UNION ALL
SELECT 'ServerC', 'DBC', 7777777, '2017-03-12 06:00:12.590', 100 UNION ALL
SELECT 'ServerD', 'DBD', 500, '2016-11-21 02:18:55.023', 1 UNION ALL
SELECT 'ServerD', 'DBD', 50000, '2017-03-12 06:00:12.590', 66 UNION ALL
SELECT 'ServerE', 'DBE', 666666, '2016-11-21 02:18:55.027', 1 UNION ALL
SELECT 'ServerE', 'DBE', 66666666, '2017-03-12 06:00:12.590', 17SELECT *
FROM (
SELECT ServerName, DBName, LEAD( TotalDiskSpace ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) - TotalDiskSpace AS Growth,
LastInserted AS StartDate, LEAD( LastInserted ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) AS EndDate,
DATEDIFF( DAY, LastInserted, LEAD( LastInserted ) OVER( PARTITION BY ServerName, DBName ORDER BY Rank1 ) ) AS DifferenceDays, Rank1
FROM @databases
) AS T
WHERE Rank1 = 1For faster and tested solutions, please provide sample data and DDL in future as I had done for this post
You can also go through the link in my signature for additional details
Since this was posted in the SQL 2008 forum, it's likely that the OP is working with SQL 2008. LEAD/LAG were introduced in SQL 2012, so this solution probably won't work.
The following code works for the limited data, but it assumes that the min/max DB size corresponds with the min/max inserted date, which is not necessarily the case.
SELECT ServerName, DBName, MAX(TotalDiskSpace) - MIN(TotalDiskSpace), MIN(LastInserted), MAX(LastInserted)
FROM @databases
GROUP BY ServerName, DBName
If that assumption is not true, then the following should work, although you may need to adjust the order in the ROW_NUMBER functions to get the correct records.;
WITH DBs AS
(
SELECT ServerName, DBName, TotalDiskSpace, LastInserted, Rank1,
ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY TotalDiskSpace, LastInserted) AS min_rn,
ROW_NUMBER() OVER(PARTITION BY ServerName, DBName ORDER BY TotalDiskSpace DESC, LastInserted) AS max_rn
FROM @databases
)
SELECT ServerName, DBName, MAX(TotalDiskSpace) - MIN(TotalDiskSpace), MIN(LastInserted), MAX(LastInserted)
FROM DBs
WHERE min_rn = 1
OR max_rn = 1
GROUP BY ServerName, DBName
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply