Re: help?

[Baron Schwartz]

Hi Trevor,

I'm CCing the MySQL list, where you may get faster response.  Also try
the #mysql IRC channel on Freenode if you need help faster.

Cheers
Baron

On Sun, Mar 23, 2008 at 2:38 PM, Trevor Smith <trevor@stripped> wrote:
> I realize that this is a huge favour to ask for free tech support but
>  this is a personal hobby site so...
>
>  I have a problem that your techniques have partially solved. I'm
>  hoping you might have advice to finish my quest.
>
>  I have these tables (simplified for example purposes):
>
>  mysql> select * from tips order by shop asc, delivery_date asc;
>  +---------------+------+--------------+------------+-------+
>  | delivery_date | shop | hours_worked | deliveries | tips  |
>  +---------------+------+--------------+------------+-------+
>  | 2008-02-29    |    4 |         6.50 |         21 | 65.25 |
>  | 2008-03-21    |    4 |         2.50 |          5 | 17.85 |
>  | 2008-03-06    |    5 |         6.00 |         15 | 51.75 |
>  | 2008-03-22    |    5 |         5.25 |         10 | 39.00 |
>  +---------------+------+--------------+------------+-------+
>  4 rows in set (0.00 sec)
>
>  mysql> select * from wages;
>  +------+------------+------+
>  | shop | start      | rate |
>  +------+------------+------+
>  |    4 | 2005-03-21 | 0.00 |
>  |    5 | 2007-01-05 | 7.00 |
>  |    5 | 2008-03-20 | 8.00 |
>  +------+------------+------+
>  3 rows in set (0.00 sec)
>
>
>  My goal is to get the wages.rate row that corresponds to each row in
>  tips.  A delivery_date in tips of 2008-03-06 is later than 2007-01-05
>  when wages.rate was set to 7.00 but BEFORE wage.rate increased to 8.00
>  on 2008-03-20 so the desired result would be like this:
>
>  +---------------+------+--------------+------------+-------+------+
>  | delivery_date | shop | hours_worked | deliveries | tips  | rate |
>  +---------------+------+--------------+------------+-------+------+
>  | 2008-02-29    |    4 |         6.50 |         21 | 65.25 | 0.00 |
>  | 2008-03-21    |    4 |         2.50 |          5 | 17.85 | 0.00 |
>  | 2008-03-06    |    5 |         6.00 |         15 | 51.75 | 7.00 |
>  | 2008-03-22    |    5 |         5.25 |         10 | 39.00 | 8.00 |
>  +---------------+------+--------------+------------+-------+------+
>
>  wages.rate for shop 4 should be 0.00 for both entries because are both
>  the same or later than the one entry in wages for shop 4;
>  wages.rate for shop 5 should be 7.00 on 2008-03-06 because that is
>  later than 2007-01-05 but BEFORE 2008-03-20;
>  wages.rate for shop 5 should be 8.00 on 2008-03-22 because that is
>  later than 2007-01-05 AND later than 2008-03-20 (when the wage
>  increased to 8.00).
>
>  I can use a combination of your self-join tip from
> 
> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
>  and a *specific, fixed* date to get the wages.rate row that is in
>  effect at that *specific* date. For example:
>
>  mysql> SELECT tips.*, w.rate AS wage FROM tips
>      -> LEFT JOIN
>      -> ((SELECT shop, MAX(start) AS startdate FROM wages WHERE start
>  <= "2008-03-21" GROUP BY shop) AS x INNER JOIN wages AS w ON w.start =
>  x.startdate AND w.shop = x.shop)
>      -> ON (x.shop=tips.shop)
>      -> ORDER BY shop ASC, delivery_date ASC;
>  +---------------+------+--------------+------------+-------+------+
>  | delivery_date | shop | hours_worked | deliveries | tips  | wage |
>  +---------------+------+--------------+------------+-------+------+
>  | 2008-02-29    |    4 |         6.50 |         21 | 65.25 | 0.00 |
>  | 2008-03-21    |    4 |         2.50 |          5 | 17.85 | 0.00 |
>  | 2008-03-06    |    5 |         6.00 |         15 | 51.75 | 7.00 |
>  | 2008-03-22    |    5 |         5.25 |         10 | 39.00 | 7.00 |
>  +---------------+------+--------------+------------+-------+------+
>  4 rows in set (0.00 sec)
>
>  note that I'm using hard coding the date with which to select the rate
>  out of wages.  For shop 4 that's 0.00 and for shop 5 that's 7.00 for a
>  hard coded date of 2008-03-21.
>
>  What I need to figure out is how can I use the delivery_date for
>  *each* row in the WHERE clause inside that inner join?
>
>  So what I *want* is something like this:
>
>  mysql> SELECT tips.*, w.rate AS wage FROM tips
>      -> LEFT JOIN
>      -> ((SELECT shop, MAX(start) AS startdate FROM wages WHERE start
>  <= tips.delivery_date GROUP BY shop) AS x INNER JOIN wages AS w ON
>  w.start = x.startdate AND w.shop = x.shop)
>      -> ON (x.shop=tips.shop)
>      -> ORDER BY shop ASC, delivery_date ASC;
>
>  but that generates:
>
>  ERROR 1054 (42S22): Unknown column 'tips.delivery_date' in 'where
>  clause'
>
>
>  any ideas?
>
>
>  BTW, the reason I'm not just using a start and end date range for each
>  pay level is that makes the primary key difficult (impossible?) to
>  restrict from within in MySQL for overlapping ranges.
>
>  --
>  Trevor Smith    |    I'm the pig now.
>
>
>