add and subtract column in query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: add and subtract column in query

  1. #1
    Join Date
    Dec 2003
    Posts
    90

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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you post a sample data, and your pl/sql proc?

    Tamil

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 . . . . )

  4. #4
    Join Date
    Dec 2003
    Posts
    90
    I don't have the db on this computer but here is what the data looks like.

    Code:
    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.
    Last edited by luciffer; 07-06-2004 at 01:14 PM.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Dec 2003
    Posts
    90
    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?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  8. #8
    Join Date
    Dec 2003
    Posts
    90
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  10. #10
    Join Date
    Dec 2003
    Posts
    90
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width