Get count of events from DB for a period of time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get count of events from DB for a period of time

Author

Topic

DeLarcco
Starting Member

4 Posts

Posted - 2011-03-18 : 10:55:16

Hi,

I have a question how to get cout of events from DB dynamically. I have a simple table EVENTS with TIMESTAMP, EVENTS, DIALOG_ID. I would like to count DIALOG_ID in some period of time like:

select count(distinct(DIALOG_ID))
from EVENTS
where TIMESTAMP Between '2011-02-15 16:00:00' And '2011-02-15 16:02:00'

I would like to have this Between TIMESTAMP dynamically generated and also data to be in a format easy to be copied to excel.

Can someone help me with a sample?

I tried this for a beginning:

DECLARE @intMin1 INT, @intMin2 INT, @intHour1 INT
SET @intMin1 = 0
SET @intMin2 = 2
SET @intHour1 = 16
WHILE (@intMin2 <60)
BEGIN
select count(distinct(DIALOG_ID)) as time
from EVENTS
where TIMESTAMP Between '2011-02-15 16:'+ Cast(@intMin1 as varchar)+':00' And '2011-02-15 '+ Cast(@intHour1 as varchar)+':'+ Cast(@intMin2 as varchar)+':00'
SET @intMin1 = @intMin1 + 2
SET @intMin2 = @intMin2 + 2
END
GO

Thanks for help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 11:05:28

[code]
select min(TIMESTAMP) as TIME_FROM,
max(TIMESTAMP) as TIME_TO,
count(distinct(DIALOG_ID)) as time
from EVENTS
where TIMESTAMP between '2011-02-15 16:00' and '2011-02-15 17:00'
group by datediff(minute, '2011-02-15 16:00', TIMESTAMP ) / 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DeLarcco
Starting Member

4 Posts

Posted - 2011-03-18 : 11:19:53

Excellent! It is working.

Thanks a million. It looks so simple now.

What about if i wanted something like this:

DIALOG_ID | 0-2 | 2-4 | 4-6 | ......
--------------------------------
15.03 | 120 | 57 | 33 |
--------------------------------
16.03. | 12 | 22 | 45 |
----------------------------------
......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources