title: Generate a list of dates between 2 dates for more than one record
body: <p>I am trying to write SQL to generate the following data</p>
  
  <pre><code>Date         Count
  2018-09-24   2
  2018-09-25   2
  2018-09-26   2
  2018-09-27   2
  2018-09-28   2
  2018-09-29   1
  </code></pre>
  
  <p>A sample of the base table I am using is</p>
  
  <pre><code>ID      StartDate   EndDate
  187267  2018-09-24  2018-10-01
  187270  2018-09-24  2018-09-30
  </code></pre>
  
  <p>So I'm trying to get a list of dates between 2 dates and then count how many base data records there are in each date.</p>
  
  <p>I started using a temporary table and attempting to loop through the records to get the results but I'm not sure if this is the right approach.</p>
  
  <p>I have this code so far</p>
  
  <pre><code>WITH ctedaterange 
       AS (SELECT [Dates] = (select ea.StartWork from EngagementAssignment ea where ea.EngagementAssignmentId IN(SELECT ea.EngagementAssignmentId
                                                                                                              FROM EngagementLevel el INNER JOIN
                                                                                                              EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
                                                                                                              WHERE el.JobID = 15072 and ea.AssetId IS NOT NULL))
           UNION ALL
           SELECT [dates] + 1 
           FROM   ctedaterange 
           WHERE  [dates] + 1 &lt; = (select ea.EndWork from EngagementAssignment ea where ea.EngagementAssignmentId IN(SELECT ea.EngagementAssignmentId
                                                                                                              FROM EngagementLevel el INNER JOIN
                                                                                                              EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
                                                                                                              WHERE el.JobID = 15072 and ea.AssetId IS NOT NULL)))
  SELECT [Dates], Count([Dates])
  FROM   ctedaterange 
  GROUP BY [Dates]
  </code></pre>
  
  <p>But I get this error </p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>I get correct results when the job I use only generates one record in the subselect in the where clause, ie:</p>
  
  <pre><code>SELECT ea.EngagementAssignmentId
  FROM EngagementLevel el INNER JOIN
  EngagementAssignment ea ON el.EngagementLevelID = ea.EngagementLevelId
  WHERE el.JobID = 15047 and ea.AssetId IS NOT NULL 
  </code></pre>
  
  <p>generates one record.</p>
  
  <p>The results look like this:</p>
  
  <pre><code>Dates                   (No column name)
  2018-09-24 02:00:00.000 1
  2018-09-25 02:00:00.000 1
  2018-09-26 02:00:00.000 1
  2018-09-27 02:00:00.000 1
  2018-09-28 02:00:00.000 1
  2018-09-29 02:00:00.000 1
  2018-09-30 02:00:00.000 1
  2018-10-01 02:00:00.000 1
  </code></pre>