List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:September 16 2012 10:44pm
Subject:Making myself crazy...
View as plain text  
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 — and everyone around you — will benefit from. -- Dean Van
Leuven
:::: Jan Steinman, EcoReality Co-op ::::




Thread
Making myself crazy...Jan Steinman16 Sep
  • RE: Making myself crazy...Rick James17 Sep
    • Re: Making myself crazy...Jan Steinman19 Sep
      • RE: Making myself crazy...Rick James19 Sep