The last 2 queryes that I posted were proven to be wrong after a little
testing.
I've just looked on Ed's queryes.
It can't last 2 hours on a database like you described(it takes a few
seconds on my db). Maybe you nedd a "vacuum analyze" and an update to
max_fsm_pages - I had the same problem in the past.
Our queryes are not the same. The main reason is that I try to extract
and the remaining value in the inventory account for the products.
This is necessary for me because I must match the inventory account from
the inventory with the sold from the balance.
This query seems to work fine.
The qty should be correct. The value will be correctly printed if you
don't have reverse ar invoices - witch will be supported in the next
version.
The assembly must be correcty printed(the components will be displayed).
SELECT id, description, partnumber, sum(qty) as qty, sum(value) as value
FROM (SELECT p.id, p.description, p.partnumber, -sum(i.qty) as qty, 0 as
value FROM invoice i JOIN ar a ON (a.id=i.trans_id) JOIN parts p ON
(i.parts_id=p.id AND p.inventory_accno_id>0) GROUP BY p.id,
p.description, p.partnumber UNION ALL SELECT p.id, p.description,
p.partnumber, 0, -sum(acc.amount) as value FROM acc_trans acc JOIN parts
p ON (p.inventory_accno_id=acc.chart_id AND p.inventory_accno_id>0) JOIN
invoice i ON (i.id=acc.invoice_id AND i.parts_id=p.id) WHERE
acc.trans_id NOT IN (SELECT id FROM ap) GROUP BY p.id, p.description,
p.partnumber UNION ALL SELECT p.id, p.description, p.partnumber,
-sum(i.qty) as qty, -sum(i.qty*i.sellprice) as value 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) GROUP BY p.id, p.description, p.partnumber) AS
temp WHERE description = GROUP BY id, description, partnumber HAVING
sum(value)!=0 OR sum(qty)!=0;
This query relies on the correct values inserted in the invoice_id field
from acc_trans table.
I dont't have the correct values for this field yet, so there I must
update it first and then run some other tests.
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?
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Ledger-smb-devel mailing list
Ledger-smb-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
