title: How do I create a third table from two tables?
body: <p><strong>I have two great tables that I would like to use as foundation for an even greater third table!</strong></p>
  
  <p>The output depends on date interval, like 09/01/2010 - 09/03/2010</p>
  
  <p>Example of Output from <strong>TABLE A</strong>   - -  (<strong>ALLOCATED</strong> Testers)</p>
  
  <pre><code>Date            Country        Allocated testers
  09/01/2010      Nigeria           0
  09/02/2010      Nigeria           1
  09/03/2010      Nigeria         134
  
  09/01/2010      China             2
  09/02/2010      China             0
  09/03/2010      China            14
  
  09/01/2010      Chile             3
  09/02/2010      Chile             4
  09/03/2010      Chile             0
  
  *************
  </code></pre>
  
  <p>Example of Output from <strong>TABLE B</strong>   - -  (<strong>ABSENT</strong> Testers)</p>
  
  <pre><code>Date            Country        Absent testers
  09/01/2010      Nigeria           0
  09/02/2010      Nigeria           7
  09/03/2010      Nigeria           0
  
  09/01/2010      China             2
  09/02/2010      China             0
  09/03/2010      China             0
  
  09/01/2010      Chile             1
  09/02/2010      Chile             0
  09/03/2010      Chile             0
  
  *************
  </code></pre>
  
  <p>Example of WANTED output from TABLE C  (ALLOCATED AND ABSENT Testers)</p>
  
  <pre><code>Date            Country        Allocated testers    Absent testers
  09/01/2010      Nigeria           0                 0
  09/02/2010      Nigeria           1                 7
  09/03/2010      Nigeria         134                 0
  
  09/01/2010      China             2                 2
  09/02/2010      China             0                 0
  09/03/2010      China            14                 0
  
  09/01/2010      Chile             3                 2
  09/02/2010      Chile             4                 0
  09/03/2010      Chile             0                 0
  </code></pre>
  
  <p>And here is some SQL code generating the above shown output... (yes, they work)</p>
  
  <p><strong>TABLE A</strong></p>
  
  <pre><code>WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date
       UNION ALL
       SELECT     DATEADD(d, 1, Date) AS Expr1
       FROM         Calendar AS Calendar_1
       WHERE     (DATEADD(d, 1, Date) &lt; @EndDate))
      SELECT     C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]
       FROM         Calendar AS C CROSS JOIN
                              Country AS C2 LEFT OUTER JOIN
                              Requests AS R ON C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID
       GROUP BY C.Date, C2.Country OPTION (MAXRECURSION 0)
  </code></pre>
  
  <p><strong>TABLE B</strong></p>
  
  <pre><code>WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date
       UNION ALL
       SELECT     DATEADD(d, 1, Date) AS Expr1
       FROM         Calendar AS Calendar_1
       WHERE     (DATEADD(d, 1, Date) &lt; @EndDate))
      SELECT     C.Date, C2.Country, COALESCE (COUNT(PA.PeopleID), 0) AS [Absent testers]
       FROM         Calendar AS C CROSS JOIN
                              Country AS C2 INNER JOIN
                              Roles AS R INNER JOIN
                              People AS P ON R.RolesID = P.RolesID ON C2.CountryID = P.CountryID LEFT OUTER JOIN
                              PeoplesAbsence AS PA ON C.Date BETWEEN PA.StartDate AND PA.EndDate AND P.PeopleID = PA.PeopleID
       WHERE     (R.Role = 'Tester')
       GROUP BY C.Date, C2.Country OPTION (MAXRECURSION 0)
  </code></pre>
  
  <p><strong>TABLE C</strong> is the one I need help to create :-)</p>
  
  <p>NOTE: I would like to see some kind of example code, to get started!</p>
  
  <p>NOTE ALSO: I don´t want to solve this with Views in SQL Server, because it just doesn´t work... - I need that 3:rd table ;-)</p>
  
  <hr>
  
  <p><strong>SOLVED!</strong> Got the solution from <strong>tdammers</strong> (thanks!), and this is how it looks when implemented:</p>
  
  <pre><code>WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date
  UNION ALL
  SELECT
      DATEADD(d, 1, Date) AS Expr1
  FROM
      Calendar AS Calendar_1
  WHERE
      (DATEADD(d, 1, Date) &lt; @EndDate))
  
  
  SELECT a.Date, a.Country, a.[Allocated testers], b.[Absent testers] FROM ( SELECT
      C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]
  FROM
      Calendar AS C
  CROSS JOIN
      Country AS C2
  LEFT OUTER JOIN
      Requests AS R 
      ON
          C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID
  GROUP BY
      C.Date, C2.Country ) as a LEFT OUTER JOIN ( SELECT     C.Date, C2.Country, COALESCE (COUNT(PA.PeopleID), 0) AS [Absent testers]
       FROM         Calendar AS C CROSS JOIN
                              Country AS C2 INNER JOIN
                              Roles AS R INNER JOIN
                              People AS P ON R.RolesID = P.RolesID ON C2.CountryID = P.CountryID LEFT OUTER JOIN
                              PeoplesAbsence AS PA ON C.Date BETWEEN PA.StartDate AND PA.EndDate AND P.PeopleID = PA.PeopleID
       WHERE     (R.Role = 'Tester')
       GROUP BY C.Date, C2.Country ) as b ON a.date = b.date AND a.country = b.country
  </code></pre>