calculate the change in multiple values over time

Question

Post reply

calculate the change in multiple values over time

jfriedl

SSC Enthusiast

Points: 120
More actions
June 12, 2012 at 12:15 pm

#262842

Hi all,
Im not quite sure how to start scripting this so Im looking for someone to point me in the right direction.
I have a table that writes the transaction log size every two hours for all databases.
What I need is to generate the change in transaction log size over 10 days for each database. For example
my data looks like
[RecordDate] [LogicalName] [SizeMB]
2010-01-01 msdb 10
2010-01-01 anotherdb 300
2010-01-02 msdb 12
2010-01-02 anotherdb 5000
...
Any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Hi and welcome to SSC! The first step to finding your answer is to read the first article linked in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

sample data

CREATE TABLE #Table([RecordDate] DATETIME , [LogicalName] sysname, [SizeMB] INT );

go

INSERT INTO #Table([RecordDate], [LogicalName], [SizeMB])

SELECT '2010.01.01 10:20:000','msdb', 10 UNION ALL

SELECT '2010.01.01 10:20:000', 'anotherdb', 300 UNION ALL

SELECT '2010.01.01 12:20:000' ,'msdb', 12 UNION ALL

SELECT '2010.01.01 12:20:000' ,'anotherdb', 5000

go

SELECT

T1.[RecordDate]

,T1.[LogicalName]

,T1.[SizeMB]

, ISNULL(T1.[SizeMB] - T2.[SizeMB],0) AS [Growth]

FROM

#Table T1

LEFT OUTER JOIN

#Table T2

ON T1.[LogicalName] = T2.[LogicalName]

AND T1.[RecordDate] = DATEADD (hh , 2 , T2.[RecordDate])

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 3

Daxesh Patel

Hall of Fame

Points: 3208

June 13, 2012 at 12:27 pm

#1500592

Sorry I miunderstood the question, removing my code

OTF SSCertifiable Points: 6104 More actions · Answer 4

jfriedl (6/12/2012)
Hi all,
Im not quite sure how to start scripting this so Im looking for someone to point me in the right direction.
I have a table that writes the transaction log size every two hours for all databases.
What I need is to generate the change in transaction log size over 10 days for each database. For example
my data looks like
[RecordDate] [LogicalName] [SizeMB]
2010-01-01 msdb 10
2010-01-01 anotherdb 300
2010-01-02 msdb 12
2010-01-02 anotherdb 5000
...
Any ideas?

Always a good idea to post sample data and ddl as mentioned if you want more targeted responses.

Stab in the dark but maybe this can help:

SELECT LogicalName,

MIN(SizeMB)StartOfWeekDBSize,

MAX(SizeMB)EndOfWeekDBSize,

MAX(SizeMB) - MIN(SizeMB)ChangeInTLogSizeForWeek

FROM #Table

GROUP BY

LogicalName;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

CELKO (6/13/2012)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
CREATE TABLE Databases_Log
(log_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
database_name VARCHAR(255) NOT NULL,
PRIMARY KEY (log_date, database_name),
database_size INTEGER NOT NULL
CHECK (database_size >= 0));
SELECT database_name, log_date, database_size,
(database_size
- LAG (database_size, 10)
OVER (PARTITION BY database_name
ORDER BY log_date)
AS database_size_delta
FROM Databases_Log;

Mr. Celko,

If you are going to post an answer, please make sure it works for the version of Microsoft SQL Server for which the problem is posted. Your solution above won't work in SQL Server 2008.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

440692 I am just a number (6/13/2012)
sample data
CREATE TABLE #Table([RecordDate] DATETIME , [LogicalName] sysname, [SizeMB] INT );
go
INSERT INTO #Table([RecordDate], [LogicalName], [SizeMB])
SELECT '2010.01.01 10:20:000','msdb', 10 UNION ALL
SELECT '2010.01.01 10:20:000', 'anotherdb', 300 UNION ALL
SELECT '2010.01.01 12:20:000' ,'msdb', 12 UNION ALL
SELECT '2010.01.01 12:20:000' ,'anotherdb', 5000
go
SELECT
T1.[RecordDate]
,T1.[LogicalName]
,T1.[SizeMB]
, ISNULL(T1.[SizeMB] - T2.[SizeMB],0) AS [Growth]
FROM
#Table T1
LEFT OUTER JOIN
#Table T2
ON T1.[LogicalName] = T2.[LogicalName]
AND T1.[RecordDate] = DATEADD (hh , 2 , T2.[RecordDate])

How does that meet the OPs requirement of a difference in size every 10 days?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

jfriedl (6/12/2012)
Hi all,
Im not quite sure how to start scripting this so Im looking for someone to point me in the right direction.
I have a table that writes the transaction log size every two hours for all databases.
What I need is to generate the change in transaction log size over 10 days for each database. For example
my data looks like
[RecordDate] [LogicalName] [SizeMB]
2010-01-01 msdb 10
2010-01-01 anotherdb 300
2010-01-02 msdb 12
2010-01-02 anotherdb 5000
...
Any ideas?

Yes... since you're collecting data every two hours, make your data example look like it actually is. My recommendation would be to see the first link in my signature line below and attach a couple of weeks worth of data in that form.

Otherwise, all I can tell you is to grab the midnight time for each day for each DB, drop them into a numbered temp table, and do a self join 10 days apart to get your growth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)