Geek Answers Handbook

Required query - determining the number of rows per minute

To keep things simple, let's say that I have a SQL Server 2008 table with one column with a date and time data type. I need a query that produces the number of rows for each minute interval. For example, the results would look like this:

7/3/2011 14:00:00 | 1000 7/3/2011 14:01:00 | 1097 7/3/2011 14:02:00 | 569

The first line will mean that 1000 lines have a datetime value between 7/3/2011 13:59:00 and 7/3/2011 14:00:00.

The second line will mean that 1097 lines have a datetime value between 7/3/2011 14:00:00 and 7/3/2011 14:01:00.

The third line will mean that 569 lines have a datetime value between 7/3/2011 14:01:00 and 7/3/2011 14:02:00.

Thanks.

+4
source share
4 answers

It:

 ;WITH CTE_ExampleData as ( select stamp = '07/07/2011 14:00:01' union select stamp = '07/07/2011 14:00:02' union select stamp = '07/07/2011 14:00:03' union select stamp = '07/07/2011 14:01:01' union select stamp = '07/07/2011 14:01:02' union select stamp = '07/07/2011 14:01:03' union select stamp = '07/07/2011 14:01:04' union select stamp = '07/07/2011 14:02:02' union select stamp = '07/07/2011 14:02:03' union select stamp = '07/07/2011 14:02:04' union select stamp = '07/07/2011 14:02:05' ) select stamp = dateadd(mi,datediff(mi,0,stamp) + 1,0), rows = count(1) from CTE_ExampleData group by dateadd(minute,datediff(mi,0,stamp)+1,0)

Returns

 stamp rows 2011-07-07 14:01:00.000 3 2011-07-07 14:02:00.000 4 2011-07-07 14:03:00.000 4
+11
source

Simple group By:

  Select DateAdd(minute, DateDiff(minute, 0, [colName]), 0), Count(*) From [tableName] Group By DateAdd(minute, DateDiff(minute, 0, [colName]), 0)

If you want every minute within a certain range of output, regardless of whether there is any data at that minute, use the expression Common Table Expression (CTE):

  Declare @startMinute smalldatetime Set @startMinute = '30 June 2011' Declare @endMinute smalldatetime Set @endMinute = '1 July 2011'; With minuteList(aMinute) As (Select @startMinute Union All Select dateadd(minute,1, aMinute) From minuteList Where aMinute < @endMinute) Select aMinute, Count(T.[colName]) From minuteList ml Left Join [tableName] T On DateAdd(minute, DateDiff(minute, 0, T.[colName]), 0) = aMinute Group By aMinute Option (MaxRecursion 10000);
+10
source

You can also get the average per minute if someone wants you to say, tell them on average how long your ETL will collect X records.

 SELECT AVG(ABC.TOTAL_MINUTE) FROM (Select DateAdd(minute, DateDiff(minute, 0,createdon),0) AS [DAY_MINUTE], Count(*) AS [TOTAL_MINUTE] From contactbase Group By DateAdd(minute, DateDiff(minute, 0, createdon), 0)) ABC here

Or, if you want more information, you can give interest to complete it all ... put it in proc ... you get this idea.

 DECLARE @TtlToProcess AS DECIMAL --- put arbitrary number of 2.5 million in as this is aproximate, could be replaces with true source count if known. SET @TtlToProcess = 2500000 SELECT DATEDIFF(MINUTE,MIN(CB.CreatedOn),max(CB.CreatedOn)) AS [Minutes Run], ROUND(100*(CAST(COUNT(*) AS DECIMAL)/CAST(@TtlToProcess AS DECIMAL)),1) AS [%Complete], ROUND(100-(100*(CAST(COUNT(*) AS DECIMAL)/CAST(@TtlToProcess AS DECIMAL))),1) AS [% Left], COUNT(*) AS [Rows Processed], @TtlToProcess-COUNT(*) AS [Rows Left], (SELECT AVG(ABC.TOTAL_MINUTE) FROM (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CreatedOn), 0) AS [DAY_MINUTE], COUNT(*) AS [TOTAL_MINUTE] FROM ContactBase GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CreatedOn), 0)) AS ABC) AS [Avg. Rows per Minute], ROUND(((@TtlToProcess-COUNT(*))/(SELECT AVG(ABC.TOTAL_MINUTE) FROM (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CreatedOn), 0) AS [DAY_MINUTE], COUNT(*) AS [TOTAL_MINUTE] FROM ContactBase GROUP BY DATEADD(MINUTE,DATEDIFF(MINUTE, 0, CreatedOn), 0)) ABC))/CAST(60 AS DECIMAL),2) AS [Est. Hours Left],DATEADD(hh,((@TtlToProcess-COUNT(*))/(SELECT AVG(ABC.TOTAL_MINUTE) FROM (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CreatedOn), 0) AS [DAY_MINUTE], COUNT(*) AS [TOTAL_MINUTE] FROM ContactBase GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CreatedOn), 0)) AS ABC))/CAST(60 AS DECIMAL),GETDATE()) AS [Est. Complete Date_Time] FROM dbo.ContactBase AS CB
+1
source 
 Select DateAdd(minute, DateDiff(minute, 0, createdon), 0) AS [DAY_MINUTE], Count(*) AS [TOTAL_MINUTE] From contactbase Group By DateAdd(minute, DateDiff(minute, 0, createdon), 0) order by DateAdd(minute, DateDiff(minute, 0, createdon), 0) desc
0
source

More articles:


All Articles