Count Number of rows added within a certain time period

Question

Count Number of rows added within a certain time period

gregalbrecht

SSC Enthusiast

Points: 160
More actions
August 23, 2011 at 8:39 pm

#246780

Hi,
I would like to know how to select/count the number of rows added in my table in a given period eg:
how many rows were created every 10 seconds
EntCode, AnythingID, WhenCreated
LINFO, 12345, 2011-08-01 00:01:10
LINFO, 12345, 2011-08-01 00:01:15
LINFO, 12345, 2011-08-01 00:01:17
LINFO, 12345, 2011-08-01 00:01:19
LINFO, 12345, 2011-08-01 00:01:22
LINFO, 12345, 2011-08-01 00:01:25
LINFO, 12345, 2011-08-01 01:02:03
LINFO, 12345, 2011-08-01 01:02:05
LINFO, 12345, 2011-08-01 01:02:07
LINFO, 12345, 2011-08-01 01:02:11
LINFO, 12345, 2011-08-01 01:02:15
LINFO, 12345, 2011-08-01 01:02:20
Thanks in advance
Greg

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Greg, you'll get better and quicker responses if you format the data in a "readily consumable" format. See the first link in my signature line below for how to do that.

--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)

gregalbrecht SSC Enthusiast Points: 160 More actions · Answer 2

Okay thanks Jeff,

CREATE TABLE [MyTable]

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Enterprise nvarchar(255) NOT NULL,

AnythingID nvarchar(128) NOT NULL,

WhenCreated datetime NULL

)

INSERT INTO MyTable VALUES

(ID, Enterprise, AnythingID, WhenCreated)

SELECT '1', 'LINFO', '123456789', '2011-08-22 00:01:02' UNION ALL

SELECT '2', 'LINFO', '123456789', '2011-08-22 00:01:05' UNION ALL

SELECT '3', 'LINFO', '123456789', '2011-08-22 00:01:10' UNION ALL

SELECT '4', 'LINFO', '123456789', '2011-08-22 00:01:15' UNION ALL

SELECT '5', 'LINFO', '123456789', '2011-08-22 00:01:20' UNION ALL

SELECT '6', 'LINFO', '123456789', '2011-08-22 00:01:21' UNION ALL

SELECT '7', 'LINFO', '123456789', '2011-08-22 00:01:23' UNION ALL

SELECT '8', 'LINFO', '123456789', '2011-08-22 00:01:26' UNION ALL

SELECT '9', 'LINFO', '123456789', '2011-08-22 00:01:30'

LutzM SSC Guru Points: 107049 More actions · Answer 3

In order to know the daet range we'll need a start date.

The end date can either be limited by the number of rows, an additional date parameter or by limiting the number of values returned from cte_tally. It depends on the business rule.

Speaking of cte_tally: if you don't use a Tally table yet you might want to have a look at the related link in my signature. A Tally table comes in handy in various scenarios including the one you describe.

DECLARE @start DATETIME

SET @start='2011-08-22 00:01'

SELECT @start

;

WITH cte_tally AS

(

SELECT

DATEADD(ss,number*10,@start) AS START,

DATEADD(ss,(number+1)*10,@start) AS finish

FROM master..spt_values WHERE type ='P'

)

SELECT START, COUNT(*) AS cnt

FROM cte_tally

INNER JOIN mytable

ON whencreated>=start AND whencreated < finish

GROUP BY START

ORDER BY START

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Greg,

Did Lutz's answer do it for you?

--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)

Devil's Bloody Advocate SSC-Addicted Points: 451 More actions · Answer 5

If you dont mind gaps then

declare @start_date datetime = '2011-08-22 00:00:00'

select DATEADD( ss , Offset , @start_date)

, COUNT(*)

from (

select Offset = datediff( ss , @start_date, WhenCreated ) / 10

from MyTable

) as xx

group by Offset

order by Offset

Or using a characteristic function (very old school)

declare @start_date datetime = '2011-08-22 00:00:00'

select Offset = DATEADD( ss , datediff( ss , @start_date, WhenCreated ) / 10 , @start_date)

, COUNT(*)

from MyTable

group by DATEADD( ss , datediff( ss , @start_date, WhenCreated ) / 10 , @start_date)

order by 1

Should just do 1 table scan, dont know which would be faster on big record sets.

Enjoy...