SQLTeam.com | Weblogs | Forums

Count transactions every 30 mins


Hi All,

I need help on how to count transactions on every 30 minutes
Here's the needed column names ID, starttime, dialedNumber, contactID I think on my callTable.

Please help.


Are you trying to collect data every 30 minutes or collect all data but group by 30 second intervals? If its the former, create a SQL Agent Job and schedule it to run every 30 seconds. If its the latter, something along these lines:[code]declare
@CallTable table(
starttime datetime

insert into @CallTable (starttime)
values (GetDate()),
(DATEADD(minute, 1, GETDATE())),
(DATEADD(minute, 31, GETDATE())),
(DATEADD(minute, 35, GETDATE()))

SELECT 'Raw Date', * FROM @CallTable;

'Grouped Data', DATEADD(hour, DateDiff(hour, 0, starttime), 0), COUNT(*)
group by
DATEADD(hour, DateDiff(HOUR, 0, starttime), 0),
when DatePart(minute, starttime) between 0 and 30
then 1
else 2


Thanks Stephen! I was able to solved this.