title: Merging dummy/placeholder data into a SQL query
body: <p>In our SQL Server 2016 database we have a <code>Payments</code> table which records monthly payments made by our customers, but as they do not necessarily pay every month we may have missing month data.  </p>
  
  <p>I now need to insert the missing monthly payment data (i.e a zero payment) for each customer for an SSRS report because the business wants to see every month on the report to assess customer payment frequency.</p>
  
  <p>So in the SQL statement below, I first create a table variable and insert a row for each month and a zero payment amount. Then I've created some sample payment data with customer Id, month paid and the amount. What I then need to do is end up with a result that has 12 entries for each customer, one for each month, showing either the payment made that month or 0.</p>
  
  <pre><code>-- Dummy monthly payment data to use for missing months
  DECLARE @DummyPayments TABLE
  (
      MonthNumber INT,
      Payment MONEY
  )
  
  INSERT INTO @DummyPayments 
  select 1,0 union
  select 2,0 union
  select 3,0 union
  select 4,0 union
  select 5,0 union
  select 6,0 union
  select 7,0 union
  select 8,0 union
  select 9,0 union
  select 10,0 union
  select 11,0 union
  select 12,0
  
  -- This (much simplified) data would come from our Payments table
  DECLARE @CustomerPayments TABLE
  (
      CustomerID INT,
      MonthNumber INT,
      Payment MONEY
  )
  
  -- Example customer 1 made payment in months 1,3,6,9
  insert into @CustomerPayments values(1,1,100);
  insert into @CustomerPayments values(1,3,120);
  insert into @CustomerPayments values(1,6,140);
  insert into @CustomerPayments values(1,9,95);
  
  -- Example customer 2 made payment in months 2,5,10,12    
  insert into @CustomerPayments values(2,2,80);
  insert into @CustomerPayments values(2,5,90);
  insert into @CustomerPayments values(2,10,130);
  insert into @CustomerPayments values(2,12,105);
  
  -- Now I want to join real payments with dummy/missing payments
  -- to get payment data for each month in the year.
  with cust as
  (
      select distinct CustomerID 
      from @CustomerPayments
  )
  select * from @CustomerPayments cp
  union
  select c.CustomerID, 
  (select dp.MonthNumber
   from @DummyPayments dp 
   where dp.MonthNumber not in (select cp.MonthNumber from @CustomerPayments cp where cp.CustomerID = c.CustomerID)),
   0
  from cust c
  </code></pre>
  
  <p>When I run it I get an 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 thought doing this with a union would work and I understand the error is telling me I'm getting too many results in each sub-query but short of using a cursor I can't work out how to do this. Perhaps I'm over complicating it but if anyone can help me out I'd appreciate it.</p>