title: SQL Server query joining a table with the results of another query
body: <p>I'm working with a datatable containing incident tickets (<code>dbo.IncidentDimvw</code> referenced in the first variable declaration)  </p>
  
  <p>I'm trying to produce a query that will enumerate every date from the earliest ticket, until present day, along with the numbers of tickets "opened" and "closed" on each day (which may be 0), and the number of tickets "<em>open</em>" on each day (different from "opened").</p>
  
  <p>To accomplish this, I need a list of dates (i.e. a calendar) that is independent from my ticket datatable. I was able to use some CTEs to do the job, building the list of dates on the fly within a query.  </p>
  
  <p>This part works flawlessly, and you can test my code below if you'd like to see it in action (supply a constant value for <code>@SSOReportDateRangeBegin</code>).</p>
  
  <p>My current query is shown below, with descriptive comments. It returns a complete calendar, with one row for each date from 12/27/2012 to the current date (scalable up to ten years).</p>
  
  <p>The problem I'm encountering is, I can't figure out how to join <code>dbo.IncidentDimvw</code> to this "calendar", to pull the counts of tickets opened, closed, and <em>open</em> on each date.<br>
  The CTEs and <code>DISTINCT</code> selection are making it difficult for me to understand how these joins need to be structured.  </p>
  
  <p>What I really want, is to have the final <em>result</em> of the calendar CTEs selected as a plain old dataset, from which I can compare tickets - thereby separating the logic of the calendar creation from the logic of ticket selection.</p>
  
  <p>I've tried <code>LEFT JOIN</code>-ing <code>dbo.IncidentDimvw</code> to the calendar query, but when I select<br>
  <code>COUNT(OpenedInc.ID) AS 'Incidents Opened'</code> the resulting counts are way too large - which indicates to me that the calendar logic is interfering with the ticket selection logic - or perhaps I don't understand how to <code>GROUP</code> correctly in this situation.</p>
  
  <pre><code>/* Begin our date range with the earliest
     incident ticket creation date on record. (currently 12/27/2012)
     CAST is necessary because the CreatedDate column is a datetime value,
     and we specifically want to truncate the "time" portion
     for the purposes of this query.*/
  DECLARE @SSOReportDateRangeBegin AS DATE =
      (SELECT MIN(CAST(inc.CreatedDate AS DATE)) FROM dbo.IncidentDimvw inc);
  
  /* State how many years to include in our date range.
     Currently our range includes 10 years. (12/27/2012 - 12/26/2022)
     Later on, our selection will limit results to only dates &lt;=CAST(GETDATE() AS DATE) */
  DECLARE @SSOReportDateRangeYears AS INT = 10;
  /* @SSOReportDateRangeYears cannot be greater than 31.
     For larger time periods, add CTEs for decades, centuries, millenia, etc. */
  
  /* Calendar calculater based on a row counter with 31 rows.
     Calculates day 1 through day 31 for all 12 months, for 10 years.
  
     Since some months don't have 31 days, some DATEADD calculations
     will rollover to the next month, causing duplicate date values.
     (e.g. Feb 31st will be interpreted as Mar 3rd in non-leap year,
     Mar 2nd in leap-year, duplicating the date values for March 2nd or 3rd)
  
     SELECT DISTINCT is used to eliminate duplicate date values,
     giving us a clean and complete calendar to work with. */
  WITH [counter](N) AS
  (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1),
  SSOReportDays(N) AS (SELECT row_number() OVER (ORDER BY (SELECT NULL)) FROM [counter]),
  SSOReportMonths(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N &lt;= 12),
  SSOReportYears(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N &lt;= @SSOReportDateRangeYears)
  
  SELECT DISTINCT
  
  CAST(DATEADD(DAY, SSOReportDays.n,
       DATEADD(MONTH, SSOReportMonths.n,
       DATEADD(YEAR, SSOReportYears.n,
       DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) AS CalendarDate
       /* Subtract 1 day from @SSOReportDateRangeBegin,
          because the counter will begin with the following day. */
  
  /* CROSS JOIN to compute every possible combination
     of day(1-31) month(1-12) and year(1-10) */
  FROM SSOReportYears CROSS JOIN SSOReportMonths CROSS JOIN SSOReportDays
  
  /* Reduce calendar to &lt;= the current date,
     since incident tickets cannot be created with future dates. */
  WHERE CAST(DATEADD(DAY, SSOReportDays.n,
             DATEADD(MONTH, SSOReportMonths.n,
             DATEADD(YEAR, SSOReportYears.n,
             DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) &lt;= CAST(GETDATE() AS DATE)
             /* Subtract 1 day from @SSOReportDateRangeBegin,
                because the counter will begin with the following day. */
  </code></pre>