[wellylug] [OT] PHP - Oracle - Query Help Desperatly Needed ;-)

Nick Jensen nickspoon at gmail.com
Tue Sep 27 14:04:55 NZST 2005


On 9/27/05, Brent Wood wrote:
> My 02c...
>
> in which case a subquery may be more efficient than an inner join, eg:
> ...
> where tblCustomer.Customer in (select distinct Customer from TTCCOM100099)
>   and ....

Agreed, certainly more readable this way

> also, the nested floors simply gives more room for rounding errors to
> accumulate, so I'd suggest just retaining the outer FLOOR and just have the
> SUM's in the inner part, unless some accounting arcana requires the extras.

Also agreed. So I end up with this:

SELECT
	r.Description,
	SUM(s.DeliveredQuantity),
	SUM(s.SalesValue),
	FLOOR(SUM(s.SalesValue) - SUM(s.Cost)),
	FLOOR(((SUM(s.SalesValue) - SUM(s.Cost)) / SUM(s.SalesValue)) * 100),
	c.Region,
	(SELECT
		SUM(DeliveredQuantity)
	 FROM
		SALES_HISTORY
	 WHERE
		YEAR(Sale_Date) = 2004 AND
		Customer = c.Customer),
	(SELECT
		SUM(SalesValue)
	 FROM
		SALES_HISTORY
	 WHERE
		YEAR(Sale_Date) = 2004 AND
		Customer = c.Customer)
FROM
	tblSalesOrder so INNER JOIN
	tblSales s ON so.SalesOrderNumber = s.SalesOrderNumber INNER JOIN
	tblCustomer c ON so.Customer = c.Customer INNER JOIN
	tblRegion r ON c.Region = r.Region
WHERE
	c.Customer IN
		(SELECT DISTINCT Customer FROM TTCCOM100099) AND
	so.SalesRep = '". $rep ."'
GROUP BY
	r.Description, v.Region
ORDER BY
	FLOOR(SUM(d.SalesValue)) DESC

This gives the units and value, but I don't know how you can calculate
the profit because the SALES_HISTORY table doesn't appear to contain
the Cost.




More information about the wellylug mailing list