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
You can see the SQL here if you wish:
Then click on the "Source" link to see the code. (You won't be able to change it without
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 — and everyone around you — will benefit from. -- Dean Van Leuven
:::: Jan Steinman, EcoReality Co-op ::::