Dear Experts,
Good day. I am looking for some assistance in building a query. I have a Access 2007 Database having a just one table, namely "CDR". The Field structure of the Table is as follows (3 Fields):
Terminator - Text
CountryCode - Number
Duration - Number
This table will contain Data of call information terminated via multiple carriers and the durations. I am trying to write a query to calculate the percentage of connected calls which is less than 60 seconds for any given CountryCode and group it by the terminator.
Currently what I do is, write 2 separate queries as follows:
1) select terminator, count(duration) as dc from CDR where duration > 0 and CountryCode = 44 group by Terminator;
2) select terminator, count(duration) as dc from CDR where duration > 0 and duration < 60 and CountryCode = 44 group by Terminator;
Then I process both the queries at application level to calculate the precentage of calls less than 60 seconds (dropped calls) for each Terminator. My application output is something like:
Terminator Name / Total Connect Calls / Total Calls < 60 / Drop %
Terminator-1, 100, 45, 45%
Terminator-2, 250, 100, 40%
Terminator-2, 562, 62, 11.03%
Now, what I would like to do is, rather than having 2 queries and processing the drop percentage at application level, I would like to know if there is a way to combine both of these queries and get a similar output in one go - which would allow me to move away the calculations from application level to database level.
Any help on this would be much appreciated.
Rgds
Shaf.
Start Free Trial
