I've got a query that works but I"m convinced is needlessly complicated. I'll try to keep this simple.

I've got an orders table and a product history table. I need to join the order date to the most recent price in the product history table.

public.orders

pk,- -logdate------- -----product_id----- ----qty

1-- -2016-01-01-- ------widget1-------- ----1

2-- -2016-06-01- ------widget1-------- -----1

3-- -2016-11-01- ------widget1-------- -----1

public.product_history

pk- -change_date----- ----product---- ---cost

11- -2016-01-01------- ----widget1--- ----1.00

12- -2016-09-01------- ----widget1--- ----1.25

13- -2016-11-01------- ----widget1--- ----1.50

INTENDED RESULT

orders.logdate, -orders.product, -orders.qty,- -product_history.cost,-- -product_history.pk

2016-01-01----- -----widget1----- -----1------- --1.00

2016-06-01- ------widget1-------- -----1------- --1.00

2016-11-01- ------widget1-------- -----1------- --1.50

Here's my ugly query that works

with good_start as (

select t.logdate,t.part_desc,sum(qty),max(p.id) as max_id

from orders as t

left JOIN product_history as p on p.part_desc = t.desc and p.logdate <= t.logdate

where not exists(

select id from product_history p1

  where p1.part_desc = p.part_desc

   and p1.logdate <= p.logdate

    and p1.logdate > p.logdate

     limit 1
              ) and t.part_desc = 'widget1'  and t.logdate >= '2016-01-01' and t.logdate < '2016-12-01'

group by logdate,part_desc

order by logdate

) select y.*,x.shipping_cost, x.item_description from good_start as y inner join = product_history as x on y.max_id = x.id