Usage Query

Question

Usage Query

maxlezious

SSCommitted

Points: 1648
More actions
August 24, 2015 at 3:01 am

#307034

Hi,
I have a table which gets updated with the usage figure every week.
I was wondering if anyone already has a similar t-sql which returns the increase in usage percentage of all the columns.
Thank you in advance

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You need to set up something to capture the information at regular intervals and compare to previous snapshots. That something could vary between a custom SQLAgent job to a custom Data Collector collection item.

Which one are you more comfortable with?

BTW... those table names... my eyes! Do yourself a favour and don't use the Hungarian Notation!

-- Gianluca Sartori

maxlezious SSCommitted Points: 1648 More actions · Answer 2

Thanks for your reply!

I currently have a job setup which runs the stored procedure every week and inserts the usage figures in a table. All i am looking for is a query which I can run and it will show me the percentage of usage increase since last week. Any ideas?

Eric M Russell SSC Guru Points: 125518 More actions · Answer 3

maxlezious (8/24/2015)
Thanks for your reply!
I currently have a job setup which runs the stored procedure every week and inserts the usage figures in a table. All i am looking for is a query which I can run and it will show me the percentage of usage increase since last week. Any ideas?

You can leverage the LAG() function to link record(s) for each week to the prior week and calculate a percentage.

https://msdn.microsoft.com/en-us/library/hh231256.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

And if you don't need the time, just use the date datatype instead of a datetime.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

maxlezious SSCommitted Points: 1648 More actions · Answer 5

Eric M Russell (8/24/2015)
maxlezious (8/24/2015)
Thanks for your reply!
I currently have a job setup which runs the stored procedure every week and inserts the usage figures in a table. All i am looking for is a query which I can run and it will show me the percentage of usage increase since last week. Any ideas?
You can leverage the LAG() function to link record(s) for each week to the prior week and calculate a percentage.
https://msdn.microsoft.com/en-us/library/hh231256.aspx

Thanks for your reply! Any example would be great help.

maxlezious SSCommitted Points: 1648 More actions · Answer 6

Luis Cazares (8/24/2015)
And if you don't need the time, just use the date datatype instead of a datetime.

Thank you, will change to 'date' datatype as wont be needing time.

Eric M Russell SSC Guru Points: 125518 More actions · Answer 7

maxlezious (8/24/2015)
Eric M Russell (8/24/2015)
maxlezious (8/24/2015)
Thanks for your reply!
I currently have a job setup which runs the stored procedure every week and inserts the usage figures in a table. All i am looking for is a query which I can run and it will show me the percentage of usage increase since last week. Any ideas?
You can leverage the LAG() function to link record(s) for each week to the prior week and calculate a percentage.
https://msdn.microsoft.com/en-us/library/hh231256.aspx
Thanks for your reply! Any example would be great help.

The link to documentation for LAG() function that I provided above, it includes an example for linking each record to prior based on date column. You can figure out the additional percentage calculation; it's just dividing prior by current.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

maxlezious SSCommitted Points: 1648 More actions · Answer 8

Eric M Russell (8/24/2015)
maxlezious (8/24/2015)
Eric M Russell (8/24/2015)
maxlezious (8/24/2015)
Thanks for your reply!
I currently have a job setup which runs the stored procedure every week and inserts the usage figures in a table. All i am looking for is a query which I can run and it will show me the percentage of usage increase since last week. Any ideas?
You can leverage the LAG() function to link record(s) for each week to the prior week and calculate a percentage.
https://msdn.microsoft.com/en-us/library/hh231256.aspx
Thanks for your reply! Any example would be great help.
The link to documentation for LAG() function that I provided above, it includes an example for linking each record to prior based on date column. You can figure out the additional percentage calculation; it's just dividing prior by current.

I will give it a go thanks