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?
--
Regards,
Ashley J Gittins
web: http://www.purple.dropbear.id.au
jabber: [EMAIL PROTECTED]
select
id,
partnumber,
description,
onhand,
sold,
bought,
-bought+-sold as net
from
(select *,
(select COALESCE(sum(i.qty), 0)
FROM invoice i
JOIN ar a ON (a.id = i.trans_id)
where i.parts_id = p.id
and transdate <= '2007-08-01') as sold,
(select COALESCE(sum(i.qty), 0)
FROM invoice i
JOIN ap a ON (a.id = i.trans_id)
where i.parts_id = p.id
and transdate <= '2007-08-01') as bought
from parts p
where assembly=false
) as foo
where bought+sold <> 0
order by description
-------------------------------------------------------------------------
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
