From: Jan Steinman
Date: September 16 2012 10:44pm
Subject: Making myself crazy...
List-Archive: http://lists.mysql.com/mysql/228166
Message-Id: <2B277275-DCBD-4104-BF72-25F686B4D070@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

I'm having trouble figuring out how to make a query. It seems simple, =
but it's making me crazy right now. Please point out where my thinking =
is addled...

I have the following (simplified) table structure:

s.timelog --> s.projects --> s.profit_centres

s.product.sales --> s.products --> s.profit_centres

(The arrows refer to many-to-one relationships: each Timelog record =
refers to a single Project, which is in a single Profit Centre. Each =
record has an opaque ID referred to by records in the table to its =
left.)

What I want to do is figure out productivity: sales per hour worked per =
Profit Centre.

I can do this at a gross level -- without grouping by Profit Centres -- =
with a subquery: simply sum up the Lales and divide by the sum of the =
labour (Timelog.out - Timelog.in). But I suspect even this can be done =
without a subquery.

But needing to do two levels of indirection has stymied me: how can I =
group $/hour by Profit Centre?

You can see the SQL here if you wish:
	=
http://www.ecoreality.org/wiki/Template:Annual_gross_productivity_for

Then click on the "Source" link to see the code. (You won't be able to =
change it without logging in.)

I'm using CASE to pivot tables to separate out monetary versus =
barter/trade income.

Thanks for any help offered!

----------------
:::: Respond with love and compassion, rather than punishment and =
retaliation, and an angry person will be disarmed. Trade the need to be =
right for a loving relationship. It is a trade you =97 and everyone =
around you =97 will benefit from. -- Dean Van Leuven
:::: Jan Steinman, EcoReality Co-op ::::