4 replies to this topic

[Borin]

Dear All,

I am now a student and also a staff working in one telecom company.
Now I have problem that difficult to find the solution is to make Country Report for my boss.

I have one CDR Table which is used to store all call of all subscribers in most of the country in the world. with this table my boss want me to create Country Report for him to count Total Call by each country which place in CDR Table every month. And the difficult point is some country have the same CountryCode such as USA(1) and Canada(1), Kazhastan(77) and Russia(7), Cambodia Domestic(023,054,072,....) Phone and Mobile Phone(012,013,015,016,017,018,.....etc). I have made one more table in sql name Prefix that contain all the country code and country name inside. After that use select and left join query with these two tables like the code below:

Select Code, CountryName,count(*) TotalCalls, Sum(Call_Duration_Sec) [TotalSeconds]
From CDR C INNER JOIN Prefix P ON P.Code = LEFT(Num_Out,LEN(P.Code)
Where Call_Duration_Sec>0
Group By Code, CountryName

Note: Num_Out is the field which used to stored Number of Called_Party.

I got the result with duplicate amount of record for the record that have first digit the same number. I don't know the good way to solve this problem.
So please kindly help me to solve this problem coz my experiences is not very much with sql like all of you.

The format of Country Table is:

Code CountryName TotalCalls TotalSeconds
1 USA 2123 3453243
1 Canada 1238 1786383
855 Cambodia Domestic 7864 372849008
855 Cambodia Mobile 12345 547843924
7 Russia 574 3434204
77 Kazhakstan 785 4345938
... ............ ...... ...........
... ............ ...... ...........

I am very appreciated all your kindness that always help me and others who has problem.

Thanks,

Best Regards,

[wahkiz]

Since you are getting duplicates because of the same code numbers, use a unique id that auto increments for each country to avoid that problem.

btw. this should be in d database forum.

[Borin]

Dear Sir,

Thanks for you help and answer me soon. but I still don't understand clearly about your reply. As I understand a bit and if my understanding is correct, I think I could not make a unique Country Code coz my report need to cound for each country to show result with more than one record. Each country should has result with country domestic code and country mobile code. And also canada with USA has the same country code, how can use unique country code to get result? If I use like I think I will get the result that sum this two country together, I think.

Anyway, if you understanding is wrong please give the solution and explain me clearly about your solution coz I am just graduated from school never met alot of problem with sql before that's why I don't have enough flexible ideas to solve this problem.

I am really appreciated all your help to me.

Thanks,

Best wish,

Borin.

[wahkiz]

Hey borin,

What I am suggesting is something like this
ID CODE COUNTRY
1 1 USA
2 1 Canada
3 855 Cambodia
4 7 Russia
5 77 Kazhakstan

This makes each of your fields unique, because their ID will never be the same, mysql got this function call auto increments, which assigns the new ID for each country.

Then to refer these countries in your report simply use the query
SELECT p.country, p.code FROM country p, statistic s WHERE p.id=s.country

This example assumes that you have another table named statistic, where the statistics each have a column named "country" and within that field, is the id of the country.

[Borin]

Dear Wahkiz,

Thanks for your solution and sorry for late to reply, I just saw your message today.

It work for me. :)

Very much appreciate for your kindly help.

God bless you.

Borin