0

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

Shh
  • 986
  • 9
  • 18
  • Do you want that output for one specific month or all months (and dates) that are are present in the `datetime_alloted` table? –  Feb 24 '20 at 07:23
  • Between a range of dates – Shh Feb 24 '20 at 07:26
  • It would be nice to know the reason for downvote. Will help in future. Thank You – Shh Feb 24 '20 at 08:13
  • I am thinking, if I have an extra field on the Sales table which saves the salesman's id. This can make the query simple. – Shh Feb 24 '20 at 08:44

0 Answers0