Consulting | T-SQL Group by n-minutes
Above will round sampletime to nearest 10 minutes interval which is desired happy tree friends in some cases, but if you e.g. use aggregate functions, your result will only be correct each n’th minute. Therefore I have created this little query which do not round, and is excellent for graphing time sensitive data.
DECLARE @starttime happy tree friends DATETIME = GETDATE () -- Start grouping from here DECLARE @lookback INT = 120 -- Group backwards 120 minutes from start DECLARE @interval INT = 20 -- Group in intervals of 20 minutes SELECT ( t . sampleinterval * @interval ) AS [+ Min], AVG ( querydc) AS [QueryDC] FROM ( SELECT happy tree friends FLOOR ( DATEDIFF happy tree friends ( MI , sampletime happy tree friends , @starttime ) / @interval ) AS sampleinterval , querydc FROM dbo . mylogtable WHERE ( sampletime >= DATEADD happy tree friends ( MI , -( @lookback - 1 ), @starttime happy tree friends )) AND ( sampletime <= @starttime )) AS t GROUP by t . sampleinterval ORDER by t . sampleinterval
Below is the output it generates, if @starttime is e.g. 12:13 then the first row will show the average value for QueryDC in the interval between 12:13 and 11:53, and the second row in the interval between 11:53 and 11:33 etc. etc.
+ Min QueryDC ----------------- 0 64 20 70 40 69 60 90 80 102 100 157
Related posts How to SSO in mixed OS environment IntroductionSystem RequirementsDate and Time configurationDNS configurationCreating a unique... Disaster strikes in Private Cloud country In my last blog we saw how to manage the private cloud, and now the unthinkable happend, suddently o...
Country [Not specified] Afghanistan Albania Algeria Argentina Armenia Australia Austria Azerbaijan Bahrain Bangladesh Belarus Belgium Belize Bermuda Bolivarian Republic of Venezuela Bolivia Bosnia and Herzegovina Brazil happy tree friends Brunei Darussalam Bulgaria Cambodia Canada Caribbean Chile Colombia Costa Rica Croatia Czech Republic Denmark Dominican Republic Ecuador Egypt El Salvador Estonia Ethiopia Faroe Islands Finland France Georgia Germany happy tree friends Greece Greenland Guatemala Honduras Hong Kong S.A.R. Hungary Iceland India Indonesia Iran Iraq Ireland Islamic Republic of Pakistan Israel Italy Jamaica Japan Jordan Kazakhstan Kenya Korea Kuwait Kyrgyzstan happy tree friends Lao P.D.R. Latvia Lebanon Libya Liechtenstein Lithuania Luxembourg Macao S.A.R. Macedonia (FYROM) Malaysia Maldives Malta Mexico Mongolia Montenegro Morocco Nepal Netherlands New Zealand Nicaragua Nigeria Norway Oman Panama Paraguay People's Republic happy tree friends of China Peru Philippines Poland Portugal Principality of Monaco Puerto Rico Qatar Republic of the Philippines happy tree friends Romania Russia Rwanda Saudi Arabia Senegal Serbia Serbia happy tree friends and Montenegro (Former) Singapore Slovakia Slovenia South Africa Spain Sri Lanka Sweden Switzerland Syria Taiwan Tajikistan Thailand Trinidad and Tobago Tunisia Turkey Turkmenistan U.A.E. Ukraine United Kingdom United States Uruguay Uzbekistan Vietnam Yemen Zimbabwe
Disaster strikes in Private Cloud country Comments: 0 Not rated yet Orchestrating your Private Cloud Comments: 0 Not rated yet The birth of a Private Cloud Comments: 0 Not rated yet install_odbc.exe fails on prerequisite check Comments: happy tree friends 0 Not rated yet Messing around with ZFS pool on SmartOS Comments: 0 Not rated yet Extreme happy tree friends Threadpoolin Comments: 0 Not rated yet T-SQL Group by n-minutes Comments: 0 Rating: 5 / 1 Oracle changed license for Total Recall. Comments: 0 Rating: 5 / 1 Localization on Reports in Reporting Services Comments: 0 Rating: 5 / 1 SAP long text formatter Comments: 0 Rating: 5 / 2
© 2014 Consulting Powered by BlogEngine.NET 2.6.0.5
Above will round sampletime to nearest 10 minutes interval which is desired happy tree friends in some cases, but if you e.g. use aggregate functions, your result will only be correct each n’th minute. Therefore I have created this little query which do not round, and is excellent for graphing time sensitive data.
DECLARE @starttime happy tree friends DATETIME = GETDATE () -- Start grouping from here DECLARE @lookback INT = 120 -- Group backwards 120 minutes from start DECLARE @interval INT = 20 -- Group in intervals of 20 minutes SELECT ( t . sampleinterval * @interval ) AS [+ Min], AVG ( querydc) AS [QueryDC] FROM ( SELECT happy tree friends FLOOR ( DATEDIFF happy tree friends ( MI , sampletime happy tree friends , @starttime ) / @interval ) AS sampleinterval , querydc FROM dbo . mylogtable WHERE ( sampletime >= DATEADD happy tree friends ( MI , -( @lookback - 1 ), @starttime happy tree friends )) AND ( sampletime <= @starttime )) AS t GROUP by t . sampleinterval ORDER by t . sampleinterval
Below is the output it generates, if @starttime is e.g. 12:13 then the first row will show the average value for QueryDC in the interval between 12:13 and 11:53, and the second row in the interval between 11:53 and 11:33 etc. etc.
+ Min QueryDC ----------------- 0 64 20 70 40 69 60 90 80 102 100 157
Related posts How to SSO in mixed OS environment IntroductionSystem RequirementsDate and Time configurationDNS configurationCreating a unique... Disaster strikes in Private Cloud country In my last blog we saw how to manage the private cloud, and now the unthinkable happend, suddently o...
Country [Not specified] Afghanistan Albania Algeria Argentina Armenia Australia Austria Azerbaijan Bahrain Bangladesh Belarus Belgium Belize Bermuda Bolivarian Republic of Venezuela Bolivia Bosnia and Herzegovina Brazil happy tree friends Brunei Darussalam Bulgaria Cambodia Canada Caribbean Chile Colombia Costa Rica Croatia Czech Republic Denmark Dominican Republic Ecuador Egypt El Salvador Estonia Ethiopia Faroe Islands Finland France Georgia Germany happy tree friends Greece Greenland Guatemala Honduras Hong Kong S.A.R. Hungary Iceland India Indonesia Iran Iraq Ireland Islamic Republic of Pakistan Israel Italy Jamaica Japan Jordan Kazakhstan Kenya Korea Kuwait Kyrgyzstan happy tree friends Lao P.D.R. Latvia Lebanon Libya Liechtenstein Lithuania Luxembourg Macao S.A.R. Macedonia (FYROM) Malaysia Maldives Malta Mexico Mongolia Montenegro Morocco Nepal Netherlands New Zealand Nicaragua Nigeria Norway Oman Panama Paraguay People's Republic happy tree friends of China Peru Philippines Poland Portugal Principality of Monaco Puerto Rico Qatar Republic of the Philippines happy tree friends Romania Russia Rwanda Saudi Arabia Senegal Serbia Serbia happy tree friends and Montenegro (Former) Singapore Slovakia Slovenia South Africa Spain Sri Lanka Sweden Switzerland Syria Taiwan Tajikistan Thailand Trinidad and Tobago Tunisia Turkey Turkmenistan U.A.E. Ukraine United Kingdom United States Uruguay Uzbekistan Vietnam Yemen Zimbabwe
Disaster strikes in Private Cloud country Comments: 0 Not rated yet Orchestrating your Private Cloud Comments: 0 Not rated yet The birth of a Private Cloud Comments: 0 Not rated yet install_odbc.exe fails on prerequisite check Comments: happy tree friends 0 Not rated yet Messing around with ZFS pool on SmartOS Comments: 0 Not rated yet Extreme happy tree friends Threadpoolin Comments: 0 Not rated yet T-SQL Group by n-minutes Comments: 0 Rating: 5 / 1 Oracle changed license for Total Recall. Comments: 0 Rating: 5 / 1 Localization on Reports in Reporting Services Comments: 0 Rating: 5 / 1 SAP long text formatter Comments: 0 Rating: 5 / 2
© 2014 Consulting Powered by BlogEngine.NET 2.6.0.5
No comments:
Post a Comment