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

[GoldenCockerel]

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!

[GoldenCockerel]

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

[PAVB]

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.

[GoldenCockerel]

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.

[PAVB]

... 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.