Merging 2 queries together I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before:

tdate      period   staff                      tdate     period   seats
-------------------------                    ---------------------------
20141001     9       1                       20141001      9        2
20141001     10      2                       20141001      10       4
20141001     11      7                       20141001      11       2
20141001     12      20                      20141001      14       16
20141001     13      22                      20141002      9        3
20141001     14      15                      20141002      10       7
20141002     8       1                       20141002      11       9
20141002     9       4                       20141002      12       20
20141002     10      9                       
20141002     11      12                      
20141002     12      18                      
20141002     13      14                                                                      


With this:

DECLARE @max_hr INT =(SELECT Max(CAST(LEFT(StartingTime, 2) AS INT))
  FROM   view_TimeRecords_COMBINED);

WITH cte
     AS (SELECT Min(CAST(LEFT(EndingTime, 2) AS INT)) [period]
         FROM   view_TimeRecords_COMBINED a
         UNION ALL
         SELECT [period] + 1
         FROM   cte
         WHERE  [period] &lt; @max_hr)

SELECT convert(char(8), StartingDate, 112) AS tDate, [period], Count(t.TimeKeeperStaffCode) AS Staff
        FROM cte a
        JOIN view_TimeRecords_COMBINED t
        ON a.[period] BETWEEN CAST(LEFT(StartingTime, 2) AS INT) AND CAST(LEFT(EndingTime, 2) AS INT)
        WHERE convert(char(8), StartingDate, 112) BETWEEN 20141002 AND 20141002 AND TimeKeeperJobFunctionCode NOT IN ('offic', 'maint', 'mgr', 'laun')
        GROUP  BY [period], t.StartingDate 


SELECT convert(char(8), POSChitDate, 112) AS tDate, LEFT(POSChitTime, 2) AS period, SUM(Covers) AS seats
        FROM tblPOSChits s
        WHERE convert(char(8), POSChitDate, 112) BETWEEN 20141002 AND 20141002
        GROUP BY LEFT(POSChitTime, 2), POSChitDate


I need to get to this:

tdate      period   staff  seats                     
----------------------------------                   
20141001     9       1       2               
20141001     10      2       4              
20141001     11      7       2              
20141001     12      20      0               
20141001     13      22      0              
20141001     14      15      16               
20141002     8       1       0               
20141002     9       4       3               
20141002     10      9       7                 
20141002     11      12      9                
20141002     12      18      20                
20141002     13      14      0     


I eventually will also need to group by the day,week &amp; month if you need to take that in consideration as well. It tracks the number of people working -vs- people served by hour.
Using MSSQL 2014.
I was able to figure this out by just using multiple CTE's, then selecting what I needed to combine.