We help IT Professionals succeed at work.

Query to return record by percent chance

Type25
Type25 used Ask the Experts™
on
We have a table with a list of advert campaigns, each campaign is assigned a percent value and the cumulative value will always add up to 100. Mixed in with this is a region, you can have a region of 'ALL' and different regions, 'GB', 'FR' etc..
The ALL region + the specific region must add up to no more than 100%, if it's under then we can use a default advert campaign.

For example, our table looks like this:

AdvertCampaigns
----------------------
CampaignID int identity(1,1)
CampaignName varchar(100)
Region varchar(5)
CampaignPercent int

Example Data
----------------
1, Example1, 'ALL', 70
2, Example2, 'GB', 30
3, Example3, 'FR', 20

So if you're in GB then ALL + GB add up to 100, if you're in france then FR + ALL = 90, the remaining 10% would be a generic campaign.

So, basically, i'm trying to run a function which will return a random campaignID based on the percentage chance of it coming out, but can't seem to work out the best way of doing it.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Additionally, you can have more than one 'ALL' region campaign
Top Expert 2011

Commented:
USE A TEMP TABLE

populate it with 100 rows for a region....
then select a random row


Select y.*
  into #temp
from (

select a.*
 from  Yourtable as a
   ,(select number from master..spt_values where type='p'
       and number > 0) as x
 where campaignpercent >= x.number
  and region in ('all',@region)
 union all
 sELECT A.*
   FROM (
select -1,'GENERIC',@REGION.100 - COALESCE(SUM(CAMPAIGNPERCENT),0)
 from  Yourtable as a
 where 
   region in ('all',@region)
      ) AS A
,(select number from master..spt_values where type='p'
       and number > 0) as x
 where campaignpercent >= x.number
) AS y


SELECT TOP 1 * FROM #TEMP ORDER BY NEWID()

Open in new window

Author

Commented:
Thanks for the reply, much appreciated, however i'm not sure i fully understand what's going on there. Could you write it again but with the correct field names etc...
The code below is what i've written and it works but i don't think it's the quickest way of doing it.
Thanks!

ALTER function [dbo].[fn_ReturnMainAdvert] (@Region varchar(5))
RETURNS varchar(1000)
AS
BEGIN

	DECLARE @RandomNum int
	DECLARE @Result varchar(1000)
	DECLARE @CampaignID int
	DECLARE @Seed int
	
	DECLARE @resultsTable TABLE (ID int identity(1,1), CampaignID int)
	
	DECLARE cur_  CURSOR FOR
	SELECT CampaignID, CampaignSeed FROM AdvertCampaigns WHERE Region = 'ALL' OR Region = @Region

	OPEN cur_

	FETCH NEXT FROM cur_ 
	INTO @CampaignID, @Seed

	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @i int
		SET @i = @Seed
		
		While (@i > 0) BEGIN
			INSERT INTO @resultsTable (CampaignID) VALUES (@CampaignID)
			SET @i = @i - 1
		END

		FETCH NEXT FROM cur_
		INTO @CampaignID, @Seed
		

	END

	DEALLOCATE cur_
	
	SET @RandomNum = (select * from dbo.RandomNumber)

	SET @Result = (SELECT CampaignID FROM @resultsTable WHERE ID = @RandomNum)
	
	RETURN @Result

	
END

Open in new window

Top Expert 2011
Commented:
try this


ALTER function [dbo].[fn_ReturnMainAdvert] (@Region varchar(5))
RETURNS varchar(1000)
AS
BEGIN
/*
   select a random advert from the set for the region with appropriate campaign weight

    newid is a random unique identifier
*/
	DECLARE @Result varchar(1000)	
	DECLARE @resultsTable TABLE (ID int identity(1,1), CampaignID varchar(10))

-- put 100 campaign lines into result table  campaignseed worth of each	
        insert into @resultstable (campaignid)	
	SELECT convert(varchar(10),CampaignID)
          FROM AdvertCampaigns
              ,(select number from master..spt_values where type='p'
                   and number between 1 and 100) as x
         WHERE Region in ('ALL', @Region)
           and campaignseed >= x.number
         order by newid()

--select a random row
        SELECT top 1 @result=CampaignID FROM @resultsTable order by newid()
	RETURN @Result

END

Open in new window

Author

Commented:
Nearly..
 
Invalid use of a side-effecting operator 'newid' within a functio

Author

Commented:
Absolute genius! Thank you.
Top Expert 2011

Commented:
ok try this
drop function dbo.[fn_ReturnMainAdvert] 
go
create function [dbo].[fn_ReturnMainAdvert] (@Region varchar(5))
RETURNS varchar(1000)
AS
BEGIN
/*
   select a random advert from the set for the region with appropriate campaign weight

   use the modulus of the Nanosecond part of the sysdatetime vs 101 or the first add.
*/
	DECLARE @Result varchar(1000)	
	DECLARE @resultsTable TABLE (ID int identity(1,1), CampaignID varchar(10))
    declare @d datetime2(7)
    set @d=sysdatetime()

-- put 100 campaign lines into result table  campaignseed worth of each	
        insert into @resultstable (campaignid)	
	SELECT convert(varchar(10),CampaignID)
          FROM AdvertCampaigns
              ,(select number from master..spt_values where type='p'
                   and number between 1 and 100) as x
         WHERE Region in ('ALL', @Region)
           and campaignseed >= x.number

 --select a random row
        SELECT top 1 @result=CampaignID
          FROM @resultsTable 
         where id in (ceiling((100000*convert(bigint,datepart(ns,@d)))%101),1) order by id desc
	RETURN @Result

END
go

Open in new window