title: Find Users who worked for 5 consecutive days with date-range in output
body: <p>I have a table has data similar to below</p>
  
  <pre><code>  Emp  Date        Code     
    ---  --------    ---- 
    E1  11/1/2012    W 
    E1  11/1/2012    V   
    E2  11/1/2012    W   
    E1  11/2/2012    W
    E1  11/3/2012    W
    E1  11/4/2012    W
    E1  11/5/2012    W
  </code></pre>
  
  <p>I want to get list of employees between a date range(say for the last 3 months) who worked for code W conescutively for 5 days with the date range in the output. Each employee can have multiple records for a single day with different codes.</p>
  
  <p>Expected Output is</p>
  
  <pre><code>Emp   Date-Range 
  ---   ----------
   E1   11/1 -11/5
  </code></pre>
  
  <p>Below is what I tried but I didn't come close at all to the output I seek</p>
  
  <pre><code> SELECT distinct user, MIN(date) startdate, MAX(date) enddate
  FROM (SELECT user, date, (TRUNC(date) - ROWNUM) tmpcol
        FROM (SELECT user, date
                FROM tablename
               where date between to_date('10/01/2012','mm/dd/yyyy') and to_date('10/03/2012','mm/dd/yyyy')
               ORDER BY user, date) ot) t
   GROUP BY user, tmpcol
   ORDER BY user, startdate;
  </code></pre>
  
  <p>If Emp E1 has worked for 10 consecutive days, he should be listed twice in the output with both date ranges. If E1 has worked for 9 days consecutively(11/1 to 11/9), he should be listed only once with the date range 11/1 to 11/9.</p>
  
  <p>I have already seen questions which are similar but none of them exactly worked out for me. My database is Oracle 10G and no PL/SQL.</p>