Extending on the scenario of Q80:
We want to read the correct historical price and sum up per product/day.
The price table is below…
drop table if exists vending_price ;
create table vending_price
(product_id int,valid_from date,valid_to date,price decimal (8,2),
primary key (product_id,valid_from,valid_to)) engine = innodb;
insert into vending_price
select v1.product_id,v2.d,adddate(v2.d,interval 3 day),rand() * 15 from
(select distinct product_id from vending_log) v1,
(select '2010-01-01' as d union select '2010-01-05' union
select '2010-01-09' union select '2010-01-13' union select '2010-01-17' ) v2;
[Just for fun]
This query left joins the log against the prices using the valid from/to dates.
The left join is there to catch occurences, when there is no price.
select date(v1.sold_ts) as dd,sum(v2.price),count(*), sum(if(v2.price is null,1,0)) as prices_missing from vending_log v1 left join vending_price v2 on (v1.product_id = v2.product_id and date(v1.sold_ts) between v2.valid_from and v2.valid_to) group by dd;