I want to know sales done by a each salesperson in different areas. There are many areas, and many salespersons.
They are allotted to different areas periodically. There sales figures and time of sales are recorded.
Area (table)
id name
1 A
2 B
3 G
4 D
and many more
Salesman (table)
id name
1 X
2 Y
3 Z
....and many more
datetime_alloted (table)
id area salesman datetime
1 3 2 1st Feb 2020 9:00am
2 2 2 2nd Feb 2020 9:00am
3 4 1 3rd Feb 2020 11:00am
4 3 1 3rd Feb 2020 11:00am
5 1 3 5th Feb 2020 10:00am
6 2 1 5th Feb 2020 10:00am
7 4 3 6th Feb 2020 10:00am
Sales (table)
id area sales datetime
1 3 25 1st feb 2020 1:00pm
2 2 18 3rd Feb 2020 11:am
3 3 30 2nd Feb 2020 2:00pm
4 4 24 3rd Feb 2020 2:00pm
5 4 12 3rd Feb 2020.4:00pm
6 3 20 5th Feb 2020 2:00pm
7 1 24 4th Feb.2020.11:30am
8 1 38 6th Feb 2020.3:00pm
So my output for each salesman:
salesman area. 1st Feb. 2nd Feb. 3rd Feb 5th Feb. 6th Feb
2 3 25 30 38
2 2 18
1 4 30
1 3 36 20
3 1 24
Basically, salesman wise/area wise/daywise sales by each salesman.
Even if it's not in pivot form. it will help.
EDIT: An area can only be allotted to one salesman. When an area is reallotted, it is assumed that the previous salesperson ceases to be allotted to that area