Loop Query
-
Hi,
I am new developer and I just need to run the below query for 50 countries. How can i loop this query to run for other countries just like 'India'. Any help appreciated.
select top 8 country_code,count(*) calls
from cdr_current
where auth_code IN
(select distinct auth_code from Stnley_092007_1 where country_code in
(select distinct code from LONG_DISTANCE_CODE_TABLE where country_group in ('India')))
and answer_time >= '2007-01-01'
and cdr_type=4 and sp_code=1
group by country_code
order by count(*) desc
Thanks
-
How are the 50 countries in question currently stored? A variable, in a table, on a piece of paper, etc?
Also, do you want the top 8 for each country, the top 8 countries (which is the path it looks like your code is heading down), or something else entirely?
-
Thanks.
It is in table and the query should run for each value in table just as 'India' in above query. I just don't want to run the above query for other countries 50 tims.
-
I'm still slightly confused. If you want to run it for each country individually, why are you grouping on country code, and also why are you grouping on Top 8, when it should only return a single row anyway?
Unless 'India' can have more than one country code, in which case, I'm still confused, but differently.
-
sorry for confusions. See the two queries below which exactly are same except one uses INDIA and other uses PAKISTAN. Like indian and pakistan,k I want to run this query for 50 different countries. Can u suggest how this can be done?
QUERY 1
select top 8 country_code,count(*) calls
from cdr_current
where auth_code IN
(select distinct auth_code from Stnley_092007_1 where country_code in
(select distinct code from LONG_DISTANCE_CODE_TABLE where country_group in ('India')))
and answer_time >= '2007-01-01'
and cdr_type=4 and sp_code=1
group by country_code
order by count(*) desc
QUERY 2
select top 8 country_code,count(*) calls
from cdr_current
where auth_code IN
(select distinct auth_code from Stnley_092007_1 where country_code in
(select distinct code from LONG_DISTANCE_CODE_TABLE where country_group in ('Pakistan')))
and answer_time >= '2007-01-01'
and cdr_type=4 and sp_code=1
group by country_code
order by count(*) desc
-
The quick and dirty way is to wrap a cursor around the thing (you'll hear about the evils of cursors, but it's 50 rows, so I wouldn't sweat it too much for this), storing the country name in a variable. Then, change the line in your code that reads:
select distinct code from LONG_DISTANCE_CODE_TABLE where country_group in ('India')
to
select distinct code from LONG_DISTANCE_CODE_TABLE where country_group = @CountryName
-
I haven't tested this, but it should work and NO Cursor! Except for the TOP 8 part. If you do in fact need the top 8 reply and someone or myself will work on it.
SELECT --TOP 8
country_code
,COUNT(*) AS calls
FROM
cdr_current cc
INNER JOIN Stnley_092007_1 s
ON s.auth_code = cc.auth_code
INNER JOIN LONG_DISTANCE_CODE_TABLE l
ON l.code = s.country_code
WHERE
AND answer_time >= '2007-01-01'
AND cdr_type=4
AND sp_code=1
GROUP BY
country_code
ORDER BY
COUNT(*) DESC
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Nah...too simple and way too fast... interferes with Scotch drinking time... create a double nested cursor to slow it down
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
*laughs* Well Jeff, I prefer to have my code ran and done quickly so I can go drink another...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply