quote:

---

CREATE PROCEDURE ap_get_activity_by_country

@fromdate datetime

AS

Begin

set nocount on

Create table #temp (country varchar(50), country_count int, country_count_recent int, percent_active float, percent_active_recent float, download_count int, avg_download int)

Declare Cursid Cursor for
select distinct(country) from smc_new_products.dbo.usr_smc
where country<>''

Open cursid

Declare @country varchar(50),
@country_count int,
@country_count_recent int,
@active_users int,
@percent_active float,
@percent_active_recent float,
@download_count int,
@avg_download int

Fetch next from Cursid into @country
while (@@fetch_status<>-1)
Begin

--Grab how many users are registered in each country
select @country_count=count([user_id]) from smc_new_products.dbo.usr_smc where country=@country

--Grab how many users are registered in each country
select @country_count_recent=count([user_id]) from smc_new_products.dbo.usr_smc where country=@country
and
[user_id] in
(select [user_id] from smc_new_products.dbo.usr where user_create_time between @fromdate and getdate())

--Grab how many users are active within the timeperiod provided
select @active_users=count(distinct(username)) from smc_new_products.dbo.etechmodelrequests
where requestdatetime between @fromdate and getdate()
and interfacename like '%download%'
and username in
(select convert(varchar(50), [user_id]) from smc_new_products.dbo.usr_smc where country=@country)

--Calculate Active users versus all registered users
if @active_users>0
Begin
Select @percent_active=(@active_users/@country_count)
end
else
Begin
select @percent_active=0
end

--Calculate Active users versus only users registered in specified time frame
if @active_users>0
Begin
Select @percent_active_recent=(@active_users/@country_count_recent)
end
else
Begin
select @percent_active=0
end

--Grab how many downloads
select @download_count=count(username) from smc_new_products.dbo.etechmodelrequests
where requestdatetime between @fromdate and getdate()
and interfacename like '%download%'
and username in
(select convert(varchar(50), [user_id]) from smc_new_products.dbo.usr_smc where country=@country)

--Calculate Average download per user
if @active_users>0 and @download_count>0
Begin
select @avg_download=(@download_count/@active_users)
end
else
Begin
select @avg_download=0
end


--Insert values
insert into #temp values (@country, @country_count, @country_count_recent, @percent_active, @percent_active_recent, @download_count, @avg_download)

Fetch next from Cursid into @country
end

--Clean up
deallocate cursid

select * from #temp order by country_count desc

end
GO

---

 
CREATE TABLE #DistinctCountries(Country VARCHAR(50))

INSERT INTO #DistinctCountries(Country)
SELECT distinct(country) FROM smc_new_products.dbo.usr_smc 
WHERE country <>'' 

CREATE TABLE #DistinctCountries(Country VARCHAR(50))

INSERT INTO #DistinctCountries(Country) VALUES('USA')
INSERT INTO #DistinctCountries(Country) VALUES('Canada')
INSERT INTO #DistinctCountries(Country) VALUES('England')

CREATE TABLE #Users(UserID INT IDENTITY(1,1), Country VARCHAR(50))

INSERT INTO #Users(Country) VALUES('USA')
INSERT INTO #Users(Country) VALUES('USA')
INSERT INTO #Users(Country) VALUES('USA')
INSERT INTO #Users(Country) VALUES('USA')

INSERT INTO #Users(Country) VALUES('Canada')
INSERT INTO #Users(Country) VALUES('Canada')
INSERT INTO #Users(Country) VALUES('Canada')

INSERT INTO #Users(Country) VALUES('England')
INSERT INTO #Users(Country) VALUES('England')

SELECT u.Country, count(*) AS DistinctUsersPerCountry
FROM #Users u
INNER JOIN #DistinctCountries dc ON dc.Country = u.Country
GROUP BY u.Country

DROP TABLE #Users
DROP TABLE #DistinctCountries
 

quote:

---

if @active_users>0
Begin
Select @percent_active=(@active_users/@country_count)
end
else
Begin
select @percent_active=0
end

---