Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Exclamation Unanswered: calculate total tuition by accumulating column values ( loop?)

    I am trying to create a procedure which will calculate the total tuition
    This process involves 3 tables.
    Contract table has tuition information which is all $100 (set price).
    Discount table has discount type and discount percentage (ex. 0.3) on each discount type.
    ContractDiscount table have contract number and discount number to connect both tables


    I think I need to create a loop since some contract gets more than one discount.
    I have to calculate and get result nee to be like this

    total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition

    Do you have any suggestion ?

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Are the discount percents cumulative?

    SELECT (tuition-((SUM(discountPer))*tuition))

    Or do you apply one discount, move to the next one and apply it, etc. This will make a huge difference in the final price and the way you create your procedure.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    19

    calculate total tuition by accumulating column values ( loop?)

    Yes, it is cumulative. That is why I was wondering how I do the loop.

    thanks

  4. #4
    Join Date
    Apr 2004
    Posts
    19

    Smile It worked

    I create a procedure like down below

    select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition
    from contract c, contractDiscount cd, discount d
    where c.contractNum = cd.contractNum
    and cd.discountNum =d.discountNum
    group by c.contractNum, c.tuition

    It warked thanks for your suggestion.
    result was what i was expected

    result>
    contractNum | totaltuition
    3 | 70.00
    4 | 60.00
    5 | 20.00
    .
    .
    etc

    gazawaymy
    Last edited by gazawaymy; 06-05-04 at 16:58.

Posting Permissions

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