Datedif with several entries

[ddlyturtle]

Hi,

I have been scratching my head for some time now trying to figure out how to layout my table structure. I have one table that gets data of sales imported from several databases manually but salesperson is a related key so now connected to a second table to track if the salesperson is a homeseller and what brand he/she focuses on etc. Just to expand potential ways for statistics.
eg.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Salesperson[/TD]
[TD]city[/TD]
[TD]product[/TD]
[TD]salestotal[/TD]
[/TR]
[TR]
[TD]2015-01-02[/TD]
[TD]Adam[/TD]
[TD]London[/TD]
[TD]Catnip[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Salesperson[/TD]
[TD]Location[/TD]
[TD]EmployeePeriod[/TD]
[TD]Brand[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Home[/TD]
[TD]2015-01-01[/TD]
[TD]Whiskers[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Away[/TD]
[TD]2016-03-01[/TD]
[TD]Skittles[/TD]
[/TR]
</tbody>[/TABLE]





However, I want each salesperson to have something i now call "EmployeePeriod". As they are employed elsewhere and they might change brand but i do not get this information from any imported data. So i'll have/want to manually edit this table as changes will rarely happen, instead of manually entering this to every import(which contains 60k rows).

I want the historic data to stay related to that persons row as it was related to that period. (eg. Adam sells Whiskers for 2015-01-01 - 2016-01-01) while 2016-01-02 the brand was changed to Skittles.

so for all sales from the date 2016-01-02 Adams sales will be labeled skittles, and all data for 2015-01-01 - 2016-01-01 will be still be categorized as Whiskers.


Problem 2:
With the same current tables, i'd like to track how many days that salesperson was selling whiskers depending on which date i am looking at in the pivot. To compare if numbers of sales increased with time spent within that period/brand. Can't figure a clever way to solve this.

Eg in pivot if i filter the dates between 2015-01-01 and 2015-12-31 i'll see a column with linear datedif up until a new "employeeperiod" starts. something like (=Datedif(Table1!Date, Table2!EmployeePeriod,"d") but when employeeperiod changes it becomes troublesome to somehow keep the historical data to previous employeeperiod.


I'm open for suggestions..