August 24, 2015 at 3:01 am
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
August 24, 2015 at 7:40 am
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
August 24, 2015 at 7:50 am
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?
August 24, 2015 at 8:02 am
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
August 24, 2015 at 8:06 am
And if you don't need the time, just use the date datatype instead of a datetime.
August 24, 2015 at 8:13 am
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.
Thanks for your reply! Any example would be great help.
August 24, 2015 at 8:14 am
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.
August 24, 2015 at 8:18 am
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.
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
August 24, 2015 at 8:25 am
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.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply