Please consider my tables
- store - table with information of all my stores
- zone - An alias table with zone_id and the name of the zone.
- store_zone - Table that contains the information when a store was changed to a different zone.
- cashdesk - table with local_id that contains information about the income for each day and each store
store_zone table:
+----+----------+---------+-------------+
| id | store_id | zone_id | modified_at |
+----+----------+---------+-------------+
| 1 | 1 | 2 | 2019-01-01 |
| 2 | 1 | 1 | 2019-01-04 |
| 3 | 2 | 4 | 2019-01-03 |
+----+----------+---------+-------------+
- store_id is the foreign key for my local table id
- store table also contains a fk for zone_id
- zone_id is the foreign key for my zone table id
Now consider my problem:
- I need to select all stores
- get the sum(income) per row (stores)
- filter by zone
- consider only incomes that correspond to the local_zone table between dates. (if I select the whole month and a zone was changed the day 15.. I only care about the 15 first days). I can use php to help build it.
something like:
$zone_modified_dates = //result of
"SELECT modified_at
FROM store_zone
WHERE zone_id = 1 AND store_id = 1"
$zone_modified_dates = '2019-01-04'
SELECT sum(income) from cashdesk c
inner join store s ON s.id = c.store_id
inner join store_zone sz ON (sz.store_id = s.id AND sz.zone_id = z.id
WHERE zone_id = 1
AND cashdesk created_at >= '2019-01-01'
AND cashdesk created_at >= '2019-01-10'
AND //IF ZONE WAS CHANGED IN THIS CREATED_AT BETWEEN DATES ONLY CONSIDER THE DAYS THAT THE SPECIFIC ROW STORE WAS CONSIDERED ZONE 1
GROUP BY s.id
Thanks in advance.
@edited for better explanation of the problem.
