add and subtract column in query

[luciffer]

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

[tamilselvan]

Can you post a sample data, and your pl/sql proc?

Tamil

[DaPi]

Since you don't issue a ticket when the balance is zero, why isn't it
sum(payments) - count(tickets)*price
? ? ? ?

(You do have an odd db design with both payments and ticket issues in the same table . . . . )

[luciffer]

I don't have the db on this computer but here is what the data looks like.

draws              
-----             
1  10/07/2004           
2  17/07/2004     
3  24/07/2004
4  31/07/2004

ticket_sales
-------------
ID ticket_id draw_id participant_id amount_paid
-- --------- ------- -------------- -----------
1  100       1       5000           5          
2  100       2       5000           0
3  100       3       5000           0
4  100       4       5000           2
5  200       1       6000           0
6  200       2       6000           0
7  200       3       6000           0
8  200       4       6000           5

getting the credits for ticket_id = 100 and draw_id = 4 would yield 3
getting the credits for ticket_id = 200 and draw_id = 4 would yield 4

DaPi: I hope the second case helps show why its not sum(payments) - count(tickets). As sum(payments) would be 5 and count(tickets) would be 4 which is 1 and incorrect.

[DaPi]

I don't see why rows with ID = 5,6,7 exist. Particpant 6000 had zero balance for those draws, so why was he included in them? If you exclude them, then you get the answer = 4.

I probably didn't correctly distinguish between draws & tickets (I'm not sure now what a ticket is), should it be:
sum(payments) - count(draws)*price
? ? ?

If the data really will look like that, then the definition of the balance is procedural, as function of the time sequence. In which case SQL won't answer - you'll need PL/SQL or C or something. To avoid the overhead of reading everything every time, you would probably want to de-normalise your model and store the "balance at time of draw" in another table.

[luciffer]

To avoid the overhead of reading everything every time, you would probably want to de-normalise your model and store the "balance at time of draw" in another table.

I argues that point with my co-worker but he thought it would be less efficient to store the balance in another table and have triggers to increment/decrement the balance.

A draw encompases all of the tickets that are available to win a prize, its a lotto, gambling type thing ;-)

Tickets are purchased by the participants (tickets range in numbers from 1-4000) up to 4000 can be entered in every draw.

When a participant purchases a ticket, they retain that number until it has not been used for 10 consecutive draws. This is *I think* why my co-worker was entering the unpaid draws into the ticket_sales table. So that he could find the consecutive draws easier?

[DaPi]

Originally posted by luciffer
I argues that point with my co-worker but he thought it would be less efficient to store the balance in another table and have triggers to increment/decrement the balance.

Looks like you have to pay the price somewhere . . .

Maybe if you rethink your data model it might help? I see 3 entities (draw, participant & ticket) and draw:ticket is m:n (I think) - so I would expect 4 tables.

[luciffer]

we have the following tables

participants
------------
id
name
phone

Stores
--------
id
name
phone

tickets
---------
id
status
store_id

draws
------
id
draw_date
winning_ticket_id

ticket_sales
--------------
id
draw_id
participant_id
ticket_id
amount_paid

[DaPi]

Part of the problem (I think) is that ticket_sales is doing something strange:
It records the amount_paid by participant_id for ticket_id between draw_id-1 and draw_id - i.e. the draw cycle is acting as your time-scale, rather than using a real date.

Nothing is indicating if a ticket participated in a draw. Rather than storing this as an associative entity, you are having to calculate/infer it. If you had this entity: sum(amount_paid)-count(ticket_draw_participations)*price gives your answer.

[luciffer]

it records the amount_paid because as soon as a participant pays for a ticket they retain that ticket until it is not paid for X (determined in teh system usually greater then 10) consecutive draws.

This is why we have the amount_paid and enter 0 in amount paid for all ticket_sales.

Because it needs to be calculated from the previous payments for the ticket and at the same time we calculate how many draws the ticket has gone unpaid for.