I have a scenario where we need to capture the state of the first records. The first field is the key(id) . Status can be 1(Active) or 0(Inactive), From the input, as the eff_to date for the first record is > Eff from dt of second record and the status from 28/08/2013 is still Active from the third record it will be active in the continuous period till 20/11/2013. So, effectively we will have 2 records with the eff_from_dt and eff_to_date as mentioned below.
There is a small catch which needs to be implemented here. Activa status takes more precedence. If in a time span where its inactive; if active record time span is found there had to be break as the second is made and hence the grouping had to be done. Tried different ways; could not able to achieve using OLAP. Can snyone pls help..