IntervalMatch doesn't give expected results

[Anonymous]

I have 2 tables. The Team table shows team membership and the time interval when the member was part of the team (in the example, Maggie was part of team Steve during the 1^st through 3^rd month and then she switched to team Jerry from 4^th through 6^th month). The Sales table shows qty sold by team for specific month.

Team Table:

User	Team	Start	End
Maggie	A	1	3
Maggie	B	4	6

Sales Table:

Team	Month	Qty
Steve	2	200
Jerry	2	210
Steve	5	500
Jerry	5	510

I’m trying to figure out how many sales should be assigned to a rep based on the performance of the team that the sales rep belonged to at the time (this is really for bonus calculations).

In this example, Maggie should show the following:

User	Month	Qty
Maggie	2	200
Maggie	5	510

Instead the script below gives me sum of sales for all teams over all periods. I can’t figure out whether I have a design / concept problem or syntax / how I use the IntervalMatch function. I’d appreciate any help.

[rwunderlich · Accepted solution]

Team link to Sales is made only on Team name. Generating an IntervalMatch table does not change that unless you use the IntervalMatch table to refine the link.

So...LEFT JOIN the IntervalMatch table to either the Sales or Team table.

LEFT JOIN (Team)

IntervalMatch (Month,Team)

Load

	Start,
	End,
	Team

Resident Team;

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

Thanks Rob. Now that I've read it outloud it makes sense.

Brian