Price and product query with historical prices in mysql db (3 tables)

Fairly new to mysql, and I am lost at the moment. This is my first time posting here. My problem is to make a query when I have introduced the historical table for price/kickback/commission. Before I introduced this table i had the following query doinmg the job of getting the stats:

select 
    cc_sale.cc_agent,
    individual.surname as cc_agentname,
    SUM(cc_sale.number_of) as sales,
    commoditys.commodity_name,(commoditys.commission * SUM(cc_sale.number_of)) as commission_sum 
    FROM cc_sale 
    LEFT JOIN commoditys ON commoditys.id=cc_sale.commodity_id
    LEFT JOIN destination ON destination.dest_id=cc_sale.cc_agent
    LEFT JOIN individual ON individual.individual_id=destination.owner_id
    WHERE cc_sale.project_id=$ccprojectid group by cc_sale.cc_agent,commoditys.commodity_name order by commission_sum desc;

After the introduction of the commodity_history table I struggle to find a way to query to get the same stats.

mysql> describe cc_sale;
+--------------------+---------------------+------+-----+-------------------+----------------+
| Field              | Type                | Null | Key | Default           | Ext开发者_StackOverflow社区ra          |
+--------------------+---------------------+------+-----+-------------------+----------------+
| id                 | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| project_id         | int(10) unsigned    | NO   |     | NULL              |                |
| cc_agent           | int(10) unsigned    | NO   |     | NULL              |                |
| cc_called          | int(10) unsigned    | NO   |     | NULL              |                |
| commodity_id       | int(10) unsigned    | NO   |     | NULL              |                |
| number_of          | int(10) unsigned    | NO   |     | NULL              |                |
| customer_number_of | int(10) unsigned    | YES  |     | NULL              |                |
| status             | tinyint(3) unsigned | YES  |     | 1                 |                |
| sale_date          | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+--------------------+---------------------+------+-----+-------------------+----------------+

9 rows in set (0.00 sec)

mysql> describe commoditys;
+------------------+------------------+------+-----+---------------------+----------------+
| Field            | Type             | Null | Key | Default             | Extra          |
+------------------+------------------+------+-----+---------------------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| commodity_id     | varchar(64)      | NO   |     | NULL                |                |
| commodity_name   | varchar(64)      | NO   |     | NULL                |                |
| price            | decimal(10,2)    | YES  |     | NULL                |                |
| kickback         | decimal(10,2)    | YES  |     | NULL                |                |
| commission       | decimal(10,2)    | YES  |     | NULL                |                |
| added_date       | timestamp        | NO   |     | CURRENT_TIMESTAMP   |                |
| end_of_life_date | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| cc_customer_id   | int(10) unsigned | YES  | MUL | NULL                |                |
| project_id       | int(10) unsigned | NO   |     | 0                   |                |
+------------------+------------------+------+-----+---------------------+----------------+

10 rows in set (0.01 sec)

mysql> describe commodity_history;
+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| c_id         | int(10) unsigned | NO   |     | NULL              |                |
| commodity_id | varchar(64)      | NO   |     | NULL              |                |
| price        | decimal(10,2)    | YES  |     | NULL              |                |
| kickback     | decimal(10,2)    | YES  |     | NULL              |                |
| commission   | decimal(10,2)    | YES  |     | NULL              |                |
| changed_date | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

From cc_sale:

+----+------------+----------+-----------+--------------+-----------+--------------------+--------+---------------------+
| id | project_id | cc_agent | cc_called | commodity_id | number_of | customer_number_of | status | sale_date           |
+----+------------+----------+-----------+--------------+-----------+--------------------+--------+---------------------+
|  1 |          1 |        3 |       420 |            2 |         1 |               NULL |      2 | 2011-05-30 16:00:00 |
|  2 |          1 |        3 |       421 |            2 |         2 |               NULL |      2 | 2011-05-30 16:00:00 |
|  3 |          1 |        3 |       422 |            1 |         2 |               NULL |      2 | 2011-05-30 16:00:00 |
| 77 |          1 |        3 |       804 |            1 |         2 |               NULL |      2 | 2011-06-06 15:00:00 |
| 78 |          1 |        3 |       809 |            1 |         4 |               NULL |      2 | 2011-06-06 15:00:00 |
| 79 |          1 |        3 |       823 |            1 |         2 |               NULL |      2 | 2011-06-06 15:00:00 |
| 80 |          1 |        3 |       835 |            1 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
| 81 |          1 |        3 |       835 |            2 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
| 82 |          1 |        3 |       841 |            2 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
| 83 |          1 |        3 |       852 |            3 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
| 84 |          1 |        3 |       854 |            4 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
| 85 |          1 |        3 |       862 |            4 |         1 |               NULL |      2 | 2011-06-06 15:00:00 |
+----+------------+----------+-----------+--------------+-----------+--------------------+--------+---------------------+

85 rows in set (0.00 sec)

mysql> select * from commoditys;
+----+--------------+----------------+---------+----------+------------+---------------------+---------------------+----------------+------------+
| id | commodity_id | commodity_name | price   | kickback | commission | added_date          | end_of_life_date    | cc_customer_id | project_id |
+----+--------------+----------------+---------+----------+------------+---------------------+---------------------+----------------+------------+
|  1 | test1        | testeteste     |  100.00 |    40.00 |      15.00 | 2011-05-25 11:55:45 | 0000-00-00 00:00:00 |              1 |          1 |
|  2 | test2        | telefonmøte    |    0.00 |  1500.00 |     300.00 | 2011-05-25 13:17:39 | 0000-00-00 00:00:00 |              1 |          1 |
|  3 | test3        | test3product   |  300.00 |   150.00 |      50.00 | 2011-06-04 15:53:32 | 0000-00-00 00:00:00 |              1 |          1 |
|  4 | test4        | test4product   |  600.00 |   300.00 |     100.00 | 2011-06-04 15:55:25 | 0000-00-00 00:00:00 |              1 |          1 |
|  5 | test5        | test5product   | 1000.00 |   300.00 |     100.00 | 2011-06-04 23:24:40 | 2012-06-29 00:00:00 |              1 |          0 |
+----+--------------+----------------+---------+----------+------------+---------------------+---------------------+----------------+------------+
5 rows in set (0.00 sec)




mysql> select * from commodity_history;
+----+------+--------------+---------+----------+------------+---------------------+
| id | c_id | commodity_id | price   | kickback | commission | changed_date        |
+----+------+--------------+---------+----------+------------+---------------------+
|  1 |    1 | test1        |  100.00 |    40.00 |      15.00 | 2011-05-25 11:55:45 |
|  2 |    2 | test2        |    0.00 |  1500.00 |     300.00 | 2011-05-25 13:17:39 |
|  3 |    3 | test3        |  300.00 |   150.00 |      50.00 | 2011-06-04 15:53:32 |
|  4 |    4 | test4        |  600.00 |   300.00 |     100.00 | 2011-06-04 15:55:25 |
|  5 |    5 | test5        | 1000.00 |   300.00 |     100.00 | 2011-06-04 23:24:40 |
|  6 |    2 | test2        |    0.00 |  1000.00 |     200.00 | 2011-06-01 08:00:00 |
|  7 |    1 | test1        |  200.00 |   150.00 |     100.00 | 2011-06-01 08:00:00 |
+----+------+--------------+---------+----------+------------+---------------------+
7 rows in set (0.00 sec)

Every time a commodity is stored in commoditys, a row is also added to commodity_history. This way I may have several rows in this table with same commodity_id but with different changed_date. I want to be able to query the db and get something like this:

+----------+------+----------------+----------------+
| cc_agent |sales | commodity_name | commission_sum |
+----------+------+----------------+----------------+
|        3 |   51 | telefonmøte    |       15300.00 | ! These 2 are same commodity with
|        3 |    5 | telefonmøte    |        1000.00 | ! different price
|      446 |    7 | telefonmøte    |        2100.00 |
|      448 |    2 | telefonmøte    |         600.00 |
|        3 |   40 | testeteste     |         600.00 |
|      446 |   23 | testeteste     |         345.00 |
|        3 |    2 | test4product   |         200.00 |
|      448 |    6 | testeteste     |          90.00 |
|        3 |    1 | test3product   |          50.00 |
+----------+------+----------------+----------------+
8 rows in set (0.00 sec)

Hope anyone has the answer to this problem. With regards Roar

Everything will be a lot more simple if you will add one more timestamp in commodity_history table. Instead changed_date, you need a from_date and a to_date. Handling this is simple at time of creating new records (and modifying the previous commodity_history record at the same time to set it's to_date timestamp). The searchtime query becomes very simple.

schgy2