eklavu (12/27/2012)
Looks like a challenging solutions and I want to try it. I'm not busy today because my boss is on holiday (yehey!!!) :-).Can you post some data and sample output so I can work something for today.
Hi,
Please find the example data-->
Master Table-->Store_Audit_Type
Audit_Type_Id Audit_Type_Description
1 New
2 Closed
3 ReOpen
Data in the table
StoreId Audit_Type_Id CreatedDate
1 1 1/1/2012
1 2 4/1/2012
1 3 6/1/2012
1 2 11/1/1012
2 1 1/5/2012
2 2 5/10/2012
3 1 12/1/2012
in the above example, Store 1 was newly opened on 1/1/2012 , then was closed on 4/1/2012.
It was Reopened on 6/1/2012 and again closed on 11/1/2012
Store 2 was newly opened on 1/5/2012, then closed on 5/10/2012
Store 3 was opened on 12/1/2012 ( this one is not yet closed )
Hope this will help to understand the scenario.
Thank you!!
Good luck!