November 3, 2005 at 8:03 am
Hi friends, i have the following issue for you...
On one SQL2000 with SP3, i have one database with one table, with this structure:
Servername (char,20)
DateOfData (Datetime)
DataBName (Char,20)
SizeOfDB (int)
The purpose of this table is to collect information for a lot of servers about the name(servername), the data when the inf. was collected (DateOfData), The name of the DB (DataBName), and its size (SizeOfDB) in MB
Here is and example:
SERVER01-A 2005-09-28 14:58:54.000 SAMPLEDB1 1453
I take this data every week since 3 months, and now i want to make a ranking of which databases registers the best growth in a period of time. Iam very new for SQL programming, I tried some methods but is really impossible for me. Anyone, pls, can helpme??
This is an example of what am i looking for (From a period of two dates)
Database - ServerName - %Increase (between two dates)
SampleDB1 SERVER01-A - 15%
Thanks, very very thanks.
November 7, 2005 at 8:00 am
This was removed by the editor as SPAM
November 7, 2005 at 2:12 pm
Grasshopper:
Try this, let me know if it's what you're after. Note: if all the rows for a given update/snapshot don't have *exactly* the same datetime value, you'll have to use convert() to trim off the time elements or use some slightly more convoluted syntax to get the most recent and prior rows *for each server/db*.
CREATE TABLE TableStats (
Servername char(20),
DateOfData Datetime,
DataBName Char(20),
SizeOfDB int
)
INSERT TableStats VALUES ('SERVER01-A', '2005-09-21 14:58:54.000', 'SAMPLEDB1', 1488 )
INSERT TableStats VALUES ('SERVER01-A', '2005-09-21 14:58:54.000', 'SAMPLEDB2', 1453 )
INSERT TableStats VALUES ('SERVER01-A', '2005-09-28 14:58:54.000', 'SAMPLEDB1', 1601 )
INSERT TableStats VALUES ('SERVER01-A', '2005-09-28 14:58:54.000', 'SAMPLEDB2', 1400 )
SELECT
TS.Servername,
TS.DataBName,
PS.SizeOfDB AS 'PriorSize',
TS.SizeOfDB AS 'CurrentSize',
(TS.SizeOfDB - PS.SizeOfDB) * 1.0 / TS.SizeOfDB AS 'Growth' -- "* 1.0" implicitly converts int to numeric
FROM
TableStats TS -- your table, I didn't see a name for it
JOIN TableStats PS -- same table, look at prior rows
ON PS.DateOfData = ( SELECT MAX (TS1.DateOfData)
FROM TableStats TS1
WHERE TS1.DateOfData < TS.DateOfData
)
AND PS.Servername = TS.Servername
AND PS.DataBName = TS.DataBName
WHERE
TS.DateOfData = ( SELECT MAX (TS1.DateOfData)
FROM TableStats TS1
)
Hope this helps,
John
John Hopkins
November 8, 2005 at 8:24 am
It works!!! Thanks, very thanks for help!!
November 11, 2005 at 2:26 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply