[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