9 Replies Latest reply: Dec 24, 2015 10:24 AM by Ian Tsang RSS

    Multiple IntervalMatches with common dimension and time line

    Ian Tsang

      Hi all

       

      I am in the middle of bringing some of our Workforce data together and have hit a bit of a wall when trying to see things from a point in time. I have digested most of hic's  blog info relating to Interval Match and Slowly Changing Dimensions but I'm still not getting the results I expect. Hopefully someone can help me with the wood and trees ;-)

       

      Essentially I have three tables WorkerHistory, SicknessAbsence and TDA. All tables have an Employee ID, From and To Dates. I want to merge these intervals like the partitioning example and link them to a calendar. When I select Jun 2015, I am expecting to see all staff, sickness and TDAs for the month I've selected. However I seem to be getting some strange outputs. e.g.

       

      For Employee 1, June 2015:

      They have a worker history of 01/02/2015 to 14/12/2015 = Expected

      Absence of 05/05/2015 to 08/05/2015 = NOT EXPECTED

      No TDA = Expected

       

      I have a feeling it's something to do with how I've linked the %Date field (which I will use for my master calendar link) or it is something relating to the partition in that I could legitimately have an absence with the same start and end date e.g. 13/05/2015 to 13/05/2015