How to use loop in SQL server to get values through called no. And country

Question

5.00/5 (1 vote)

See more:

i have 2 tables on contains Country code and next table contains call details, i wants to display 'country name' and 'total call rate' by using called no. from second table.
First it will substring 2 zeros from called no then it will loop through country table and find called country then its country name and and will multiply with second*call rate to get total call cost. plz guide me

Call Details table
OrigParty1 calledNo. DateOfcall seconds
4011 0024991229 Mar 31 2016 289
4160 0020111453552 Apr 19 2016 137
4120 0020115511222 Apr 10 2016 8
4120 00209792006 Apr 10 2016 9
4303 0094776071370 Apr 10 2016 180
4120 00209792006 Apr 10 2016 12
4120 002097920006 Apr 10 2016 30
4303 0094776071370 Apr 10 2016 168
4120 002097920006 Apr 10 2016 132
4120 00201274500007 Apr 10 2016 147

Country Table
ID Country CountryCode RatePerMin
248 Nepal 97 3.2
1 Afghanistan 93 4
2 Albania 355 3.5
3 Algeria 213 2.2
4 American Samoa 1684 2
5 Andorra 376 4.2
6 Angola 244 4.2
7 Anguilla 1264 4.5
8 Antarctica/Norfolk Island 672 5
9 Antigua and Barbuda 1268 4.5

What I have tried:

SELECT OrigParty1,DestParty1,SUBSTRING( DateTimeConnect ,21 , 16 ) as DateOfcall,
DATEDIFF(second, CAST(x AS DATETIME), CAST(y AS DATETIME)) AS [seconds] ,
"Call Destination" =
CASE
WHEN SUBSTRING(DestParty1 ,0 , 6 ) = 00249 THEN 'Sudan'
WHEN SUBSTRING(DestParty1 ,0 , 6 ) = 00974 THEN 'Qatar'
WHEN SUBSTRING(DestParty1 ,0 , 5 ) = 0020 THEN 'Egypt'
ELSE 'Unknown'
END
FROM Tbl_Dummy

Posted 2-May-16 22:29pm

Member 9994001

Updated 2-May-16 23:05pm

v2

Add a Solution

Comments

CHill60 3-May-16 4:31am

Do not use a loop! I'll come back with a solution shortly

Member 9994001 3-May-16 4:40am

i was using case but it will be lengthy for each country i have to write like this,SELECT OrigParty1,DestParty1,SUBSTRING( DateTimeConnect ,21 , 16 ) as DateOfcall,
DATEDIFF(second, CAST(x AS DATETIME), CAST(y AS DATETIME)) AS [seconds] ,
"Call Destination" =
CASE

WHEN SUBSTRING(DestParty1 ,0 , 6 ) = 00249 THEN 'Sudan'
WHEN SUBSTRING(DestParty1 ,0 , 6 ) = 00974 THEN 'Qatar'
WHEN SUBSTRING(DestParty1 ,0 , 5 ) = 0020 THEN 'Egypt'
ELSE 'Unknown'
END
FROM Tbl_Dummy

CHill60 3-May-16 4:50am

It doesn't help that in the sample data given none of the numbers called are in the country table - I'll invent some data

Member 9994001 3-May-16 4:53am

oh ok , just add one more row in country like this plz

ID Country CountryCode RatePerMin
64 Egypt 20 1.8

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-02T23:05:00

You have a problem with your attempt - the called Number must be a varchar column so you can use SUBSTRING but you haven't surrounded the number with quotes.

I've assumed the following data schemas:

SQL

create table [Call Details]
(
	OrigParty1 bigint,
	calledNo varchar(50),
	DateOfCall Date,
	seconds int
)
create table Country 
(
	id int,
	Country varchar(30),
	CountryCode int, 
	RatePerMin decimal(15,2)
)

And this query will get the details for each call:

SQL

select OrigParty1, CalledNo, Country, seconds * RatePerMin as TotalCost 
from [Call Details] CD
LEFT OUTER JOIN Country C 
   ON CD.calledNo LIKE '00' + CAST(C.CountryCode AS varchar) + '%'

Note there will be an issue if any of the 2 digit codes start with the same two digits as a 3-digit code. I don't think this can be the case with genuine country codes, it only became apparent because I was using made up data.

[EDIT] - I have noticed another issue in that to call a number you should first use the international dialling code, followed by the country code followed by the number. The solution I've presented here doesn't handle that situation - you would need to replace the hard-coded '00' with the appropriate code