creating Duplicate Records
-
Okay I have been banging my head on this for couple days now, well day and a half really. I was asked by my boss to come up with an application that will enroll our customers into a table and randomly choose a winner from that table. Okay that part is not hard. I have done that and it work perfectly. Now here come the twist. He added another layer of complexity by saying that he wants to give our customers extra entries based on their spending levels.
Example:
Customer A spends 10,000 get 5 entries
Customer B spends 5,000 get 4 entries
Customer C spends 1,000 get 3 entries
Customer D spends 500 get 2 entries
Customer A spends >499 get 1 entries
Now I created a table that stores what amount range gets what number of entries and I join it with the table of that I create when I query them. Okay so now I have a table that looks like this:
Customer Amoount Spent # of Entries
----------- ------------------ --------------
1 15,000 5
2 200 1
3 4,000 4
4 15,000 5
5 700 2
6 100 1
7 10,000 5
8 9,000 4
9 1,500 3
10 15,000 5
So now I need to duplicate the entries based on the “# of entries”. Can anyone suggest how to do this with out using a cursor?
I could accomplish this in .net but I was hoping to create a sp and just call it.
Thanks,
Matt
-
Can you elaborate a bit on how you intend to use the # of entries in a query? I don't quite follow you what 'entries' means in this case.
Most helpful would be a small example of tables involved with testdata and the desired output.
/Kenneth
-
SELECT Customer, n.number AS [EntryNo]
FROM [#table] c
INNER JOIN master.dbo.spt_values n
ON n.type = 'P'
AND n.number BETWEEN 1 AND 5
AND n.number <= c.[# of Entries]
since spt_values is an undocumented table it would be better to create your own numbers table
Far away is close at hand in the images of elsewhere.
Anon. -
kenneth,
I use entry as in enter their name into a contest. Based on the amount they purchase as the number of entries into the contest. the selection is still random but those who purchase a lot will have more chances of winning.
-
david,
Thank you. that worked perfectly.
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply