如何计算特定日期范围的期初和期末库存
我想计算提供日期范围内项目的期初和期末库存。
我有如下表格，
global_items:
+-----+--------+
| id | name |
+-----+--------+
| 1 | item 1 |
+-----+--------+
| 2 | item 2 |
+-----+--------+
| 3 | item 3 |
+-----+--------+
| 4 | item 4 |
+-----+--------+
| ... | ... |
+-----+--------+
my_items:
+-----+----------------+-----------+
| id | global_item_id | outlet_id |
+-----+----------------+-----------+
| 1 | 1 | 7 |
+-----+----------------+-----------+
| 2 | 2 | 7 |
+-----+----------------+-----------+
| 3 | 3 | 7 |
+-----+----------------+-----------+
| 4 | 4 | 7 |
+-----+----------------+-----------+
| ... | ... | ... |
+-----+----------------+-----------+
my_item_stocks:
+-----+------------+---------+---------------+-----------+---------------+-------+
| id | my_item_id | size_id | purchase_rate | sale_rate | opening_stock | stock |
+-----+------------+---------+---------------+-----------+---------------+-------+
| 1 | 1 | 10 | 100 | 200 | 0 | 0 |
+-----+------------+---------+---------------+-----------+---------------+-------+
| 2 | 1 | 11 | 100 | 200 | 0 | 5 |
+-----+------------+---------+---------------+-----------+---------------+-------+
| 3 | 2 | 10 | 100 | 200 | 1.05 | 1.05 |
+-----+------------+---------+---------------+-----------+---------------+-------+
| 4 | 3 | 12 | 100 | 200 | 10 | 10 |
+-----+------------+---------+---------------+-----------+---------------+-------+
| ... | ... | ... | 100 | 200 | 0 | 1 |
+-----+------------+---------+---------------+-----------+---------------+-------+
sizes:
+-----+--------+----------+
| id | name | quantity |
+-----+--------+----------+
| 10 | 750 ml | 750 |
+-----+--------+----------+
| 11 | 500 ml | 500 |
+-----+--------+----------+
| 12 | 350 ml | 350 |
+-----+--------+----------+
| ... | ... | ... |
+-----+--------+----------+然后我有如下的sales和purchases表，adding purchase更新我的物品库存，adding sale减少库存。
purchases:
+-----+------------+-----------+
| id | date | outlet_id |
+-----+------------+-----------+
| 1 | 2018-07-01 | 7 |
+-----+------------+-----------+
| 2 | 2018-07-10 | 7 |
+-----+------------+-----------+
| 3 | 2018-07-19 | 7 |
+-----+------------+-----------+
| ... | ... | ... |
+-----+------------+-----------+
purchase_items:
+-----+-------------+------------+---------+----------+
| id | purchase_id | my_item_id | size_id | quantity |
+-----+-------------+------------+---------+----------+
| 1 | 1 | 1 | 10 | 2 |
+-----+-------------+------------+---------+----------+
| 2 | 1 | 2 | 11 | 5 |
+-----+-------------+------------+---------+----------+
| 3 | 2 | 2 | 10 | 17 |
+-----+-------------+------------+---------+----------+
| 4 | 3 | 2 | 12 | 15 |
+-----+-------------+------------+---------+----------+
| 5 | 3 | 2 | 12 | 10 |
+-----+-------------+------------+---------+----------+
| ... | ... | ... | ... | ... |
+-----+-------------+------------+---------+----------+
sales:
+-----+------------+-----------+
| id | date | outlet_id |
+-----+------------+-----------+
| 1 | 2018-07-01 | 7 |
+-----+------------+-----------+
| 2 | 2018-07-10 | 7 |
+-----+------------+-----------+
| 3 | 2018-07-19 | 7 |
+-----+------------+-----------+
| ... | ... | ... |
+-----+------------+-----------+
sale_items:
+-----+-------------+------------+---------+------+
| id | sale_id | my_item_id | size_id | quantity |
+-----+---------+------------+---------+----------+
| 1 | 1 | 1 | 10 | 1 |
+-----+---------+------------+---------+----------+
| 2 | 1 | 2 | 11 | 2 |
+-----+---------+------------+---------+----------+
| 3 | 2 | 2 | 10 | 10 |
+-----+---------+------------+---------+----------+
| 4 | 3 | 2 | 12 | 5 |
+-----+---------+------------+---------+----------+
| 5 | 3 | 2 | 12 | 2 |
+-----+---------+------------+---------+----------+
| ... | ... | ... | ... | ... |
+-----+---------+------------+---------+----------+现在，对于选定的日期范围，例如从2018-07-01到2018-07-19，我希望输出如下所示，
+-----------+--------------------------+--------------------------+--------------------------+--------------------------+
| Item Name | Opening Stock | Purchase | Sale | Closing Stock |
+ +--------------------------+--------------------------+--------------------------+--------------------------+
| | 750 ml | 500 ml | 350 ml | 750 ml | 500 ml | 350 ml | 750 ml | 500 ml | 350 ml | 750 ml | 500 ml | 350 ml |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Item 1 | | | | 2 | 5 | | 1 | 2 | | 3 | 7 | |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Item 2 | 1.05 | | | 17 | | | 10 | | | 8.05 | | |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+我不是sql pro，如何实现这一点？我很困惑，因为很多相关的表格，只是想正确的方向。
我这样做是拉威尔所以请建议，即使有任何方法来实现这一点使用雄辩。
型号：globalitem，myitem，myitemstock，size，purchaseitem，sale，saleitem
非常感谢！
暂无答案！
目前还没有任何答案，快来回答吧！