Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: update query....

    I want to create a query where the system calculates the bonus for each transaction by looking up the SALES_STOCK_BONUS table to see if there is a valid bonus for the product in that transaction (when transaction falls between dateTo and dateFrom of a bonus)

    (productHeadings is a link table between the SALES_STOCK table and SALES_STOCK_BONUS)

    eg if heading.id = 25
    and had a bonus of 13 between 01/01/04 and 01/04/05
    and then had bonus 15 between 02/04/05 - 01/10/06
    ...but this query assigns the same bonus(13) for all transactions with product heading.id = 25 whateva the date....

    UPDATE SALES
    SET SALES.Bonus = SALES.Sale *(SALES_STOCK_BONUS.Bonus/100)
    FROM SALES, SALES_STOCK, SALES_STOCK_BONUS, productHeadings
    WHERE SALES.Product = SALES_STOCK.Product AND SALES.Whse = SALES_STOCK.Whse
    and productHeadings.heading_name = SALES_STOCK.heading2
    and SALES_STOCK_BONUS.id = productHeadings.id and
    (CONVERT(CHAR(10),SALES.[Date],110)>= CONVERT(CHAR(10),DateFrom,110)
    and CONVERT(CHAR(10),SALES.[Date],110) <= CONVERT(CHAR(10),DateTo,110))
    and SALES_STOCK_BONUS.Bonus is not null

    It doesnt seem to check the date for each one individually.... PLS help!!!

  2. #2
    Join Date
    Feb 2005
    Posts
    76
    seems as if it had problems with the conversion dates?!

Posting Permissions

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