Ok, this is a starting point:
Select BillItemLine.ItemLineItemRefFullName as Part, BillItemLine.ItemLineDesc as "Desc", max(BillItemLine.TxnDate) as LastPurchased, BillItemLine.ItemLineQuantity as PurchQTY, max(InvoiceLine.TxnDate) as LastSold, InvoiceLine.InvoiceLineQuantity as SoldQTY, {fn CURDATE()} - {fn CONVERT(BillItemLine.TxnDate, SQL_DATE)} as DaysInStock from BillItemLine, ItemInventory, InvoiceLine Where BillItemLine.ItemLineItemRefListID = ItemInventory.ListID and BillItemLine.ItemLineItemRefListID = InvoiceLine.InvoiceLineItemRefListID and BillItemLine.TxnDate > InvoiceLine.TxnDate group by BillItemLine.ItemLineItemRefFullName, BillItemLine.ItemLineDesc, BillItemLine.TxnDate, BillItemLine.ItemLineQuantity, InvoiceLine.TxnDate, InvoiceLine.InvoiceLineQuantity
See also: Easiest way to make report showing average time between receipt and invoice of item |