add and subtract column in query
PROBLEM:
this is for a draw that occurs every week. participants can pay for as many draws as they like. They can only pay for draws that have not occured yet though, they cannot pay for previous draws.
TABLES:
draws
------
ID int
DRAW_DATE datetime
ticket_sales
---------------
ID int
ticket_id int
draw_id int
participant_id int
amount_paid int
I want to be able to calculate the amount of credits a participant has for a given draw_id and ticket_id. This means that it has to calculate the amount paid for the ticket and subtract 1 for every draw not paid after it (stopping at 0, which means you can't just sum the column and subtract the count) until the ticket is paid for again.
Scenario 1:
If i pay $5 to start then don't pay for the next two draws and then pay $2 i would have a credit of $3.
$5 - 1 (for current draw) - 2 (next two draws not paid for) =
$2 + $2 (paid) - 1 (for current draw) =
$3 left
Scenario 2:
If i pay $2 then don't pay for 4 draws, then pay $2 I would have a credit of $1
$2 - 1 (for current draw) - 1 (for next draw) =
$0 - this person is not used for the next 2 draws
$2 - 1 (for current draw) =
$1 left
Questions:
I have this working 1 way using a cursor that loops over all entries for a given ticket and draw calculating the totals but it is very slow, especially when considering that this needs to be done for each ticket in the draw (about 4000 tickets/draw) and calculated for every draw before the current one (could be up to 60 draws).
Is there a better way to do this using straight SQL without a cursor and looping???