Hi all,
I'm in the process of implementing a service to automatically compute
the average cost of a product (based on recent purchase orders), see:
http://jira.undersunconsulting.com/browse/OFBIZ-240Ok, I'd like to set up a good view definition to efficiently perform the
weighted sum based on the quantity and price; in SQL the query should
look like this one:
SELECT
PRODUCT_ID, SUM(UNIT_PRICE * QUANTITY) / SUM(QUANTITY)
FROM
ORDER_ITEM AS OI, ORDER_HEADER AS OH
WHERE
OH.ORDER_ID = OI.ORDER_ID AND OH.ORDER_TYPE_ID = 'PURCHASE_ORDER' AND
ORDER_DATE > '2004-12-31 00:00:00.0'
GROUP BY
PRODUCT_ID
The problem/question is: how can I do this with a view-definition?
Can you help me with this?
Thanks,
Jacopo
PS: the above SQL will not take into account the cancelled items and all
the adjustments... but we will cope with them in the next step ;-)
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev
Locked
