SQLTeam.com | Weblogs | Forums

Count transactions every 30 mins


#1

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.


#2

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;

select
'Grouped Data', DATEADD(hour, DateDiff(hour, 0, starttime), 0), COUNT(*)
from
@CallTable
group by
DATEADD(hour, DateDiff(HOUR, 0, starttime), 0),
case
when DatePart(minute, starttime) between 0 and 30
then 1
else 2
end[/code]


#3

Thanks Stephen! I was able to solved this.