I wonder if this can be done with SQL...

Hi people

I wonder if anyone can help (I hope I have the right forum for this, apologies if I don´t).

I am scratching my head with this one.... :)

I have a db table called transactions.
A transaction record: tran_id, number_of_points_given, date_given....

(The field number_of_points_given can hold a value from 1 to 10)


I also have prize table with records of prizes, e.g:
A prize record: pr_id, pr_desc, pr_num_points_reqd, ....

When a prize is claimed, I need to calculate the number of transaction records that need to be deleted. The value from the prize claimed (prize.number_of_points_given) should equal the sum of X number of transaction records´ number_of_points_given.

For example, a prize is worth 10 points. A customer has 100 transaction records, each with a value in their num_points_given of 1-10. The customer claims the prize so only X transaction records need to deleted where X is the sum of the number_of_points_given in the X transaction records AND this number_of_points_given total equals the prize.num_points_reqd.

So the number of transaction records to be deleted is determined by not only the number_of_points_reqd but the sum of the transaction records´ number_of_points_given fields.


If the transaction records were each just worth 1 point, I could limit the number of transactions selected by the prize.number_points_reqd.


Any tips would be great! :)

example:

Customer A has these transaction records:

trans_id number_of_points_given
1 1
2 5
3 5
4 2
5 1

The prize that this customer A claims is worth 10 points (prize.number_points_reqd = 10)

So, I only want to delete a number of transaction records where their combined number_of_points_given = prize.number_points_reqd (10), no more, no less :)

Absolutely.

For each PRIZE row load in a cursor all TRANSACTIONS rows belonging to the current PRIZE.customer then loop into the cursor doing your math and issuing deletes on TRANSACTIONS table when appropriate.

Okay, thanks for your quick response,

So I should:

• load all transaction rows belonging to the customer who has won the prize into a cursor
  .

• Then, I should loop through the cursor aggregating the values of the transaction num_points_given.

• when I get to equal the prize.num_points_reqd I delete the transaction records marked and break out (as it where) of the cursor.

Ok, (I think). Two points though: firstly I am new to cursors so I will have to look up how to use them. Secondly, I am actually testing on MySQL (sorry if I shouldn´t have posted here), but any further clarifications would be great, thanks again.

Quote:

---

Originally Posted by GoldenCockerel

... I am actually testing on MySQL

---

I see, just a small detail... LOL Okay, since MySQL provides support for both storedprocs and cursors, general logic would be the same. For MySQL specifics better to look for a MySQL forum.