| Author |
Topic |
|
jrockfl
Posting Yak Master
223 Posts |
 Posted - 2004-07-14 : 19:28:10
|
| How can I state the order of operation in this SQL statement?I'm trying to subtract sum of hold time(seconds) from sum of durationseconds part then divide by 60 to get the number of minsSELECT SUM(TTDurationMinsPart) + SUM(TTDurationSecsPart) - SUM(TTHoldTime) / 60 AS minsin, FROM CalllogI think I have been looking at this too long... |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-14 : 19:35:32
|
| Powers/Multiplication/Division etc always come before addition/subtraction.So, what you'll need isSELECT SUM(TTDurationMinsPart) + SUM(TTDurationSecsPart) / 60 - SUM(TTHoldTime) / 60 AS minsin, FROM CalllogORSELECT SUM(TTDurationMinsPart) + (SUM(TTDurationSecsPart) - SUM(TTHoldTime)) / 60 AS minsin, FROM Calllogbut the former is more readable.Is this an SMDR application? |
 |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-14 : 20:00:58
|
| Thank you! The second one was more readable for me. What does SMDR stand for?I have to create some reports with the company phone system. The reps in calls, out calls, talk time etc. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-14 : 20:35:54
|
| It stands for Station Message-Detail Recording. Most recent PBX's have an SMDR function where call data can be streamed to a call recording database. I have worked on these in the past. It's a bit of fun.... |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-14 : 20:45:49
|
| This is my first time working on a project like this. We use a company called Inter-Tel. The call data from the phone system is saved into an Access DB. There is a program called "Call Viewer" or something that displays the call data. One of the employees opens up the program each morning to get the sale's reps call data. She then takes the numbers and drops them into Excel to create reports for management.I'm trying to work backwards and figure out how the program calculates calls in, calls out, talk time in, talk time out...etc. This way, I can automate the process. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-14 : 23:26:39
|
| What kind of dialer is this? A lot of times, you can redirect this to a SQL Server database instead. The dialer will have a manual with it that tells you exactly how it figures all the various statistics.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-14 : 23:33:31
|
| The standard for PBX call info interfaces is an SMDR data stream. SQL doesn't understand this, so the vendors normally supply some sort of app to read the SMDR data and insert it into a database. A common one (in Oz, anyway) is CallMaster. In a previous job I was mucking about creating a .NET listener that interfaced with SQL Server. But we got retrenched and that was the end of that. What would be handy if the PBX manufacturers started using streamed XML for the call data, then you could hook it directly up to SQL Server. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-14 : 23:40:56
|
| Yeah, that would be a dream. Rockwell dialers feed directly to an Oracle database. That's sooooo much better then the crappy flat files you have to deal with from Avaya. I've heard there upperend dialers feed directly to SQL Server though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-15 : 00:21:03
|
| For what a Rockwell system costs, it would want to! As always, you get what you pay for. SMDR isn't too much hassle once you get used to it. Far better than reading TAPI notifications though. |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-15 : 07:21:52
|
| I'm not sure what kind of dialer it is. I set up a DTS import to copy the table each day to the web server. Now I can use it in my "dashboard application" I have the number of calls in and numnber of calls out down correctly. My duration times are just a little off. It has duration time, hold time, ring time. So I have been playing with these times to try to matcb what their application says. |
|
|
|
|
Order of Operation in SQL statement