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 <[EMAIL PROTECTED]> 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. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
