complex randome selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex randome selection

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-06-11 : 11:21:18

I have a sp which job is to essentially take a look at a list of individuals product stored in the product_Master table, and insert a random individual product into the product_Pool_Detail table.

The constraints is the individuals selected in the product Master table can not have previously been selected and they cannot be related to by either Last Name or in the same Street Name and City. Right now , the SP generates a random product one by one..Beacuse I won;t know unitle the current row got inserted, the next row won;t know whether the record got already exists or not.... buti am trying to do this in a bulk insert not one by one ...

I am trying to create a looping logic that I can pass the parameter( let's say I want to generated 300 random products ) , so I can pass two parameters (Product Pool ID and number needed to be pulled) and generate the entire list all at once. The key being as random as possible. The each records shouldn't be the same based on the last name or Street Name and City and previously not selected..

how can I do this?

ALTER PROCEDURE [dbo].[usp_TEST]

@product_Pool_ID int = 1
AS
BEGIN

SET NOCOUNT ON;

Declare @Chosen int = 0
Declare @Product_ID int = 0
Declare @Default_Per_Diem money = 0
Declare @Default_Mileage_Rate money = 0

SELECT @Default_Mileage_Rate = pro_Master.Default_Mileage_Rate, @Default_Per_Diem = pro.Default_Per_Diem
FROM Product_Pool_Master INNER JOIN
pro_Master ON Product_Pool_Master.C_ID = pro_Master.C_ID
WHERE (Product_Pool_Master.Product_Pool_ID = @Product_Pool_ID);

WITH
Data
AS
(

Select Product_ID,
ROW_NUMBER() Over(Order By Product_Last_Name, Product_First_Name) as myrownumber,
ROUND(((COUNT(*)OVER() - 2) * RAND() + 1), 0) chosen
From Product_Master
Where Product_Served_Status = 0
AND

NOT EXISTS
(
SELECT
*
FROM
dbo.vw_ProductPool_Family_List
WHERE
vw_ProductPool_List.Product_Pool_ID = @Product_Pool_ID
AND vw_ProductPool_List.Name = product_Master.product_Name
OR ( RTRIM(vw_ProductPool_List.Street_Name) = RTRIM(product_Master.Address_Street_Name)
AND RTRIM(vw_ProductPool_List.City) = RTRIM(product_Master.Address_City) )

)

)

SELECT
@Product_ID = Product_ID
FROM
Data

WHERE
myrownumber = chosen

Insert Into Product_Pool_Details
(Product_Pool_ID, Product_ID, Product_Status_ID, Mileage_Rate, Per_Diem)
Values (@Product_Pool_ID, @Product_ID, 2, @Default_Mileage_Rate, @Default_Per_Diem)

Update Product_Master
Set Product_Served_Status = 1
Where Product_ID = @Product_ID

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 12:55:29

if you want to do bulk insert and at the same time make sure you dont include more than 1 row with related Last Name or Street Name and City then isnt it enough to group by these and take a random product from each group? if you can post some table data, we'll be able to suggest much more clearly

jung1975
Aged Yak Warrior

503 Posts

Posted - 2009-06-11 : 15:55:36

For example, if i get the list of products like below


ID             List name                 Adress                       city                   random_Num
1002             shampoo                         113 West             New york    
 1003             shampoo                        458 NW               New jeery
 10113            conditioner                    134  street          Potland
 10034           conditioner                     134  street          portland
Etc

How can I randomely pick 200 unique records where list name is not the same or adress and city is not the same..

the possible output could look like
The result set could be something like this..the key is it has to be as randome as possible and has to be uique based on the list name or address + city



ID             List name                 Adress                       city                   random_Num
1002             shampoo                         113 West             New york     
10113            conditioner                    134  street          Potland

Etc…200 records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:48:42

yup..its possible. just use something like


SELECT  ID,[List name],Adress,city
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY [List name] ORDER BY NEWID()) AS Seq,* 
FROM Table
)t
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources