Re: inventory function

[Victor Sterpu <..hidden..>]

I made a query that takes 4 seconds for a database with 3689 ap invoices and 68834 ar invoices.
My test system is a dual  xeon 2.2Gh with 2Mega RAM.
It requires 2 indexes.
CREATE INDEX parts_inventory_accno_id ON parts (inventory_accno_id);
CREATE INDEX acc_trans_invoice_id ON acc_trans (invoice_id);

And this is the query:
SELECT p.id, p.description, p.partnumber, 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) 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) GROUP BY p.id, p.description, p.partnumber;

The query is not properly tested.
The query does not support yet the assemblys, as someoane requested earlyer on the list.
I should post the function monday.

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