Since eCart already inserts the sold inventory into the orderdetails table, all you need to do is store the inventory on hand as well as the date when that inventory number was updated... then you can account for the number sold from that date in your catalog.
The query would looks something like:
SELECT *, (ProductStock - (SELECT Coalesce(Sum(DetailQuantity),0) FROM orderdetails INNER JOIN orders ON OrderID = DetailOrderID WHERE DetailProductID = ProductID AND OrderDate > ProductUpdateDate)) AS NumLeft FROM products LEFT OUTER JOIN productcategories ON ProductCategoryID = CategoryID WHERE ProductLive <> 0
At that point you could display or use the number left or use that information to optionally show an out of stock message.
Did this help? Tips are appreciated...
