Holding a state of variable

Database

Holding a state of variable

Hi,

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..

Input :

ID                STATUS    Eff from dt   Eff to dt

133332439 1              31/07/2013 27/08/2013

133332439 0              31/07/2013 31/10/2013

133332439 1              28/08/2013 28/10/2013

133332439 1              29/10/2013 20/11/2013

133332439 0              01/11/2013 20/11/2013

133332439 0              21/11/2013 22/11/2013

133332439 0              21/11/2013 31/12/9999

133332439 0              28/11/2013 28/11/2013

133332439 0              29/11/2013 02/12/2013

133332439 0              03/12/2013 16/12/2013

133332439 0              17/12/2013 05/01/2014

133331540 0              31/07/2013 27/08/2013

133331540 1              15/08/2013 31/10/2013

133331540 0              28/08/2013 28/10/2013

133331540 0              29/10/2013 20/11/2013

133331540 1              15/11/2013 20/11/2013

133331540 1              21/11/2013 22/11/2013


Output :

ID                STATUS    Eff from dt   Eff to dt

133332439 1              31/07/2013 20/11/2013

133332439 0              21/11/2013 31/12/9999

133331540 0              31/07/2013 14/08/2013

133331540 1              15/08/2013 31/10/2013

133331540 0              01/11/2013 14/11/2013

133331540 1              15/11/2013 22/11/2013

Thanks

Mohammed