Generate full category structure by date range history

Question

I'm trying to query MySQL. I have 2 tables and the data looked like this:

category_history_structure

+----------------+-----------------+----------+----+-----------+------------+------------+
| category       | parent_category | type     | id | parent_id | from_date  | to_date    |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Top level      |                 | category | 1  | 0         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Category 1     | Top level       | category | 2  | 1         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Category 2     | Top level       | category | 3  | 1         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 1 | Category 1      | category | 4  | 2         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 2 | Category 1      | category | 5  | 2         | 01.01.2013 | 01.03.2013 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Sub category 2 | Category 2      | category | 5  | 3         | 02.03.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+
| Product 1      | Sub category 2  | product  | 6  | 5         | 01.01.2013 | 01.01.2015 |
+----------------+-----------------+----------+----+-----------+------------+------------+

product_sells

+----+-----------+------+------------+
| id | product   | sell | date       |
+----+-----------+------+------------+
| 6  | Product 1 | 2    | 01.02.2013 |
+----+-----------+------+------------+
| 6  | Product 1 | 1    | 01.05.2013 |
+----+-----------+------+------------+
| 6  | Product 1 | 3    | 01.06.2013 |
+----+-----------+------+------------+

I need sells from date range 2013-01-01 - 2015-01-01 , group by Category. Trying to create a query that outputs sells by Category, the problem is that the "Sub category 2" was change there' s Parent_Category/parent_id and the results must be on 2 rows for "Product 1"

Results

+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category   | Sub category   | Product   | from_date  | to_date    | sell |
+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category 1 | Sub category 2 | Product 1 | 01.01.2013 | 01.03.2013 | 2    |
+-----------+------------+----------------+-----------+------------+------------+------+
| Top level | Category 2 | Sub category 2 | Product 1 | 02.03.2013 | 01.01.2015 | 4    |
+-----------+------------+----------------+-----------+------------+------------+------+

score 0 · Answer 1 · answered Nov 21 '13 at 17:11

Here's a sqlfiddle: http://sqlfiddle.com/#!2/cdb68/3. I put the dates in the mysql format. I assume that "01.06.2013" means "June 1, 2013" or "2013-06-01".

SELECT 
'Top level',
cat.parent_category as category, 
subcat.parent_category as subcategory, 
ps.product,
cat.from_date, cat.to_date, 
SUM(ps.sell)


FROM product_sells AS ps
LEFT JOIN category_history_structure as subcat
ON (
  ps.id = subcat.id
  AND ps.date BETWEEN subcat.from_date AND subcat.to_date
  AND subcat.type = 'product'
  )
LEFT JOIN category_history_structure as cat
ON (
  subcat.parent_id = cat.id
  AND ps.date BETWEEN cat.from_date AND cat.to_date
  AND cat.type = 'category'
  )

GROUP BY category;

There could be problems if the Top level category has also changed, but that can be fixed with another LEFT JOIN. Also, I'm assuming that products only go under subcategories and not directly under categories.

Generate full category structure by date range history

1 Answers1