This should be the query that will show the quantities also:
SELECT p.id, p.description, p.partnumber, sum(i1.qty), sum(i.qty),
sum(i.sellprice*-i.qty), sum(acc.amount),
sum(i.sellprice*-i.qty)-sum(acc.amount)
FROM invoice i
JOIN ap a ON (a.id=i.trans_id AND a.transdate<='2007-7-15')
JOIN parts p ON (i.parts_id=p.id AND p.inventory_accno_id>0)
LEFT JOIN acc_trans acc ON (acc.invoice_id=i.id AND
acc.chart_id=p.inventory_accno_id AND acc.trans_id!=a.id AND
acc.transdate<='2007-7-15')
LEFT JOIN invoice i1 ON (i1.trans_id=acc.trans_id)
GROUP BY p.id, p.description, p.partnumber;
Ashley J Gittins wrote:
On Sat, 14 Jul 2007, Charley Tiggs wrote:
One of my clients just did inventory the past two days and we needed
this feature. I ended up doing what Ed did.
Ed W wrote:
I wrote a small query to calculate the inventory and then just exported
it to a spreadsheet. However would be useful to see it in the system.
I'd love to know what you came up with wrt the query. I have mangled onhand
values in our system, and was playing the other night with a query that Ed
posted a little while ago. It took > 2.5hrs to run! 8-O I've pasted it below
if anyone has some ideas for optimisation (I am sure there are paths there,
my brain just isn't too sharp atm).
We have ~ 1,000 vendor invoices, 3,500 sales invoices, 2,000 products, 13,000
invoice lines.
Ed, Charley, were your queries much different to this version of Ed's from a
while back?
|