Forming MySql query ( compare result within rows)
    Problem statement:
    
    select all for stores , their status, phone numbers , effective date whose phone number has been changed from 2003 till date.
    
    Schema is 
    
    store_name,phone number , start_date , status
    
    sample rows
    
        abc 1234  30-DEC-2011 open 
        abc 1234  3-Jan-2012 close
        abc 3433  04-Jan-2012 close
        bbb 4444  30-Jan-2010 open
        bbb 4444  31-Jan-2011 open
    Output
    
    abc 1234 open 30-DEC-3011 till 30-DEc-2011
    abc 1234 close 31-Dec-2011 till 3-Jan-2012
    abc 3433 close 03-Jan-2012 till date
    
    I am also fine having two rows in output with sorted start date like
    
    abc 1234 30-DEC-2011 open
    abc 1234 3-Jan-2012 close    
    abc  3433 02-Jan-2012 close
    
    bbb should not be reported.

Can someone help me with this query on Oracle.I guess by using corelated queries it can be done but I am not sure how can I construct one. Please note that my table is having around 3154953 records so I also need to make sure that correlated query doesn't lock the table for whole lot of time.Is this even possible with Oracle ?



Thanks!

  
Forming MySql query ( compare result within rows)
===

    Problem statement:
    
    select all for stores , their status, phone numbers , effective date whose phone number has been changed from 2003 till date.
    
    Schema is 
    
    store_name,phone number , start_date , status
    
    sample rows
    
        abc 1234  30-DEC-2011 open 
        abc 1234  3-Jan-2012 close
        abc 3433  04-Jan-2012 close
        bbb 4444  30-Jan-2010 open
        bbb 4444  31-Jan-2011 open
    Output
    
    abc 1234 open 30-DEC-3011 till 30-DEc-2011
    abc 1234 close 31-Dec-2011 till 3-Jan-2012
    abc 3433 close 03-Jan-2012 till date
    
    I am also fine having two rows in output with sorted start date like
    
    abc 1234 30-DEC-2011 open
    abc 1234 3-Jan-2012 close    
    abc  3433 02-Jan-2012 close
    
    bbb should not be reported.

Can someone help me with this query on Oracle.I guess by using corelated queries it can be done but I am not sure how can I construct one. Please note that my table is having around 3154953 records so I also need to make sure that correlated query doesn't lock the table for whole lot of time.Is this even possible with Oracle ?



Thanks!

  