Dear colleagues!
I've got some problems with connecting data:
1. Managers and sales. Example:
Manager_history:
| Manager | Object | Manager_date_appointment |
|---|
| Alex | 123 | 26.10.2017 |
| Nick | 123 | 03.07.2018 |
| Jack | 124 | 01.08.2018 |
| Ivan | 125 | 15.10.2017 |
| Alex | 125 | 01.03.2018 |
| Jack | 125 | 13.07.2018 |
Sales:
| Object | Sales_date | Sales |
|---|
| 123 | 27.10.2017 | 100 |
| 123 | 30.11.2017 | 150 |
| 123 | 05.08.2018 | 300 |
| 123 | 06.08.2018 | 200 |
| 124 | 01.09.2018 | 500 |
| 124 | 13.10.2018 | 340 |
| 125 | 17.10.2017 | 170 |
| 125 | 19.10.2017 | 300 |
| 125 | 02.04.2018 | 650 |
| 125 | 03.05.2018 | 322 |
| 125 | 11.08.2018 | 432 |
| 125 | 10.10.2018 | 600 |
How can i get sales in the context of managers? (needed result below)
| Manager | Sum_sales |
|---|
| Alex | 100+150+650+322=1222 |
| Nick | 300+200=500 |
| Jack | 500+340+432+600=1872 |
| Ivan | 170+300=470 |
2. Building stage and sales. The same data + stages of objects with interval dates such as:
| Object | Building_stage | Stage_start | Stage_end |
|---|
| 123 | Foundation | 01.01.2016 | 04.08.2018 |
| 123 | Roof | 05.08.2018 | 01.10.2019 |
| 124 | Construction of floors | 01.01.2017 | 20.10.2018 |
| 125 | Foundation | 01.05.2016 | 13.12.2017 |
| 125 | Construction of floors | 14.12.2017 | 01.05.2018 |
| 125 | Roof | 02.05.2018 | 11.11.2019 |
The idea of needed results is the same (sales in the context of building stages):
| Building_stage | Sum_sales |
|---|
| Foundation | 100+150+170+300=720 |
| Construction of floors | 500+340+650=1490 |
| Roof | 300+200+322+432+600=1854 |
I'll be grateful for all advice. Thanks a lot.
