title: sql join and group by generated date range
body: <p>I have Table1 and I need a query to populate Table2:</p>
  
  <p><img src="https://i.stack.imgur.com/AWV4v.png" alt="enter image description here"></p>
  
  <p>Problem here is with Date column. I want to know the process of location/partner combination per day. Main issue here is that I can't pick DateCreated and make it as default date since it doesn't necessarily cover whole date range, like in this example where it doesn't have 2015-01-07 and 2015-01-09. Same case with other dates. </p>
  
  <p>So, my idea is to first select dates from some table which contains needed date range and then perform calculation for each day/location/partner combination from cte but in that case I can't figure out how to make a join for LocationId and PartnerId.</p>
  
  <p>Columns:</p>
  
  <ul>
  <li>Date - CreatedItems - number of created items where Table1.DateCreated = Table2.Date</li>
  <li>DeliveredItems - number of delivered items where Table1.DateDateOut = Table2.Date</li>
  <li>CycleTime - number of days <strong>delivered</strong> item was in the location (DateOut - DateIn + 1)</li>
  </ul>
  
  <p>I started with something like this but it's very like that I completely missed the point with it:</p>
  
  <pre><code>with d as
  (
      select date from DimDate
      where date between DATEADD(DAY, -365, getdate()) and getdate()
  ),
  
  cr as -- created items
  (
      select 
      DateCreated, 
      LocationId,
      PartnerId,
      CreatedItems = count(*)
      from Table1
      where DateCreated is not null
      group by DateCreated, 
      LocationId,
      PartnerId
  ),
  
  del as -- delivered items
  (
      select 
      DateOut, 
      LocationId,
      ParnerId,
      DeliveredItems = count(*),
      CycleTime = DATEDIFF(Day, DateOut, DateIn)
      from Table1
      where DateOut is not null
      and Datein is not null
      group by DateOut, 
      LocationId,
      PartnerId
  )
  
  select
  d.Date
  from d
  LEFT OUTER JOIN cr on cr.DateCreated = d.Date -- MISSING JOIN PER LocationId and PartnerId
  LEFT OUTER JOIN del on del.DateCompleted = d.Date -- MISSING JOIN PER LocationId and PartnerId
  </code></pre>