title: Get dates between periods
body: <p>I have a table containing the following data</p>
  
  <pre><code>periodID periostart periodend
  1        01-01-2012 10-01-2012
  2        11-01-2012 01-04-2012
  </code></pre>
  
  <p>I would like for a query to return something like this.
  periodID date</p>
  
  <pre><code>1        01-01-2012
  1        02-01-2012
  1        03-01-2012
  etc.
  1        09-01-2012
  2        11-01-2012
  2        12-01-2012
  etc.
  2        31-03-2012
  </code></pre>
  
  <p>Since I have approx 100,000 rows containing periods, I would like to work on a solution that would not influence performance (cursors, loops). Is it possible to get the result I want without using cursors or loops?</p>
  
  <p>Thanks for participatin.</p>
  
  <p><strong>I got this solution so far</strong></p>
  
  <pre><code>create table #p (id int, periodstart smalldatetime, periodend smalldatetime );
  insert into #p values 
  (1, '2012-01-01', '2015-01-10')
  insert into #p values
  (2, '2012-04-10', '2015-11-20');
  
  
  SELECT TOP 366 --aprox one year
          IDENTITY(INT,0,1) AS N
     INTO #Tally
     FROM Master.dbo.SysColumns sc1,
          Master.dbo.SysColumns sc2
  
  
  
  SELECT DATEADD(day, T.N, periodstart) AS [Date]
  FROM #p
  cross join #tally T
  WHERE (T.N &gt;= 0 AND T.N &lt; DATEDIFF(day, periodstart, periodend))
  ORDER BY [Date]
  </code></pre>