Slow Changing Dimension Sum

[#1]

I have data simlar to the following 2 tables:

Sales                                                       Office

Date        Sales    Person                         Person     Office     Start        End

1/6/11       4          Bob                              Bob          DC       1/1/11     3/1/11

1/12/11     5          Bob                              Bob          DC       2/3/11     2/8/11

3/7/11       2          Bob                              Bob          NY       3/2/11     4/8/11

3/10/11     5          Bob                              Bob          NY       3/5/11     4/2/11

                                                                  Bob          DC       4/9/11     7/1/12

So Bob was in DC until 3/1, then on 3/2 moved to NY, then moved back to DC on 4/9/11.

I need to sum up Bob's sales by office for DC and NY in total but also for various ranges, for example 2/1/11 - 4/10/11. 

I assume using Aggr on Office, but for whatever reason I am missing something, probably obvious....!

Help?

THANKS!

[emteam162]

Hirlowe123,

I'm looking for an example of a report based on SCD type 2.

Many thanks for your help.

Emteam

[jchoucq]

Hi Richard,

i think the best solution is to use the IntervalMatch function in your script.

hope this helps !

regards,

Johann