total calls at a time - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

total calls at a time

Author

Topic

jrahma
Starting Member

16 Posts

Posted - 2006-10-05 : 21:40:33

I have a table which records list of calls at a time and delete the record after the client hungs up. I want to get list of calls at a time which means I need the select count(*) for that table for every second but i want to do that without killing the database performance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 21:44:52

Can you explain more in details ?
When the records is created ?
Why delete the record after the client hungs up ?
Also provide the table structure with sample data and the result that you need

jrahma
Starting Member

16 Posts

Posted - 2006-10-05 : 21:49:39

the record will be created when the customer start making the calls and it wlll be deleted from that table (INUSE) but the calls deltails will be added to (BILLING) table with the calls details after the calls was completed.

I am currentlly running

select count(*) as total_calls from INUSE

and putting this statement in a timer in C# which tuns every second to check at the end of the day what was the maximum number of calls at a time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 21:55:55

Sorry more questions
1. "~~tuns~~ runs every second to check at the end of the day what was the maximum number of calls at a time."
- What is your definition of "maximum number of calls at a time" ?
- How do you check this currently ? select count(*) every seconds and keep track of the max count ?
- Why runs it every second ? Do you really need the infomation every second ?

jrahma
Starting Member

16 Posts

Posted - 2006-10-05 : 22:03:22

the definition of the maximum number of calls s just the sql statement I have mentioned earlier because this table will only list active calls as ended calls will be dropped from the table so the total number of records is the active calls at a time for that second.

currentlly I am just running the same mentioned sql statement in C# timer with 1second interval and writing the date_time and the select count(*) value to a CSV file then next day I am checking what was the maximum number of the select count(*) column for that day.

why running it every second? because I need to know what was the maximum number of active calls at a time which means I have to check that every second.

Just for your info: I am using SQL Server 2000.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 22:09:19

If you don't delete the record from the INUSE table at the end of the calls you will be able to obtain the information from the INUSE table. You can use a flag to identify active calls. Set the flag at the end of the calls.


select top 1 call_id, count(*) c
from   INUSE
group by call_id
order by c desc

Will this serve the purpose ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-05 : 23:31:52

Why not just insert one row in the database at the end of each call that shows the call start time and call end time for each call?

With that data it is easy to write a query that shows the total calls active each second for the whole day.


select
	a.Second,
	count(*)
from
	(
	select
		Second =
		dateadd(ss,aa.NUMBER,'20061006')
	from
		-- Function available in
		-- Script Library Forum
		F_TABLE_NUMBER_RANGE(0,86399) aa
	) a
	join
	MyCallTable b
	on a.Second between 
		b.CallStart and b.CallEnd
group by
	a.Second
order by
	a.Second

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources