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....
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
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!!!