Slow Changing Dimension Sum

Tue, 09 Apr 2013 21:20:46 GMT

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!

Re: Slow Changing Dimension Sum

emteam162 — Fri, 11 Oct 2013 13:55:14 GMT

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

Many thanks for your help.

Emteam

jchoucq — Fri, 11 Oct 2013 14:00:42 GMT

Hi Richard,

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

hope this helps !

regards,

Johann