Re: inventory function

[Ashley J Gittins <..hidden..>]

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: ..hidden..


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