title: SQL Server Query to Fill Empty Record with null or 0 Values
body: <p>I have a code in SQL Server Query that list a tabular presentation of sales of a particular tenant in PER YEAR (Column) and PER MONTH (rows) form.</p>
  
  <p>This is the part code which i think is relevant to post</p>
  
  <pre><code>SELECT tenantcode
         ,datename(month, date) [month]
         ,isnull(sum(case when year(DATE) = @Year1  then sales end), 0) as 'Year1'
         ,isnull(sum(case when year(DATE) = @Year2 then sales end), 0) as 'Year2'
         ,isnull(sum(case when year(DATE) = @Year3 then sales end), 0) as 'Year3'
         ,isnull(sum(case when year(DATE) =  @Year4 then sales end), 0) as 'Year4'
         ,isnull(sum(case when year(DATE) = @Year5 then sales end), 0) as 'Year5'
  FROM TenantSales 
  GROUP BY  datename(month,date), tenantcode
  ORBDER BY   datepart(MM,DATENAME(MONTH, DATE) + '01 2000')
  </code></pre>
  
  <p>Please note that @Year are variables formulated to get the 5 years based on user selection. In this example 2008-2012. The particular tenant started on MAY 2008, so the sales are made available only starting May 2008. The code produce this output. (For illustration)</p>
  
  <p><a href="https://i.stack.imgur.com/ecJI2.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ecJI2.jpg" alt="enter image description here"></a></p>
  
  <p>What I desire to achieve is to include all the months like this</p>
  
  <p><a href="https://i.stack.imgur.com/Yqv7m.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Yqv7m.jpg" alt="enter image description here"></a></p>
  
  <p>When the sales of the tenant starts or available on January onwards, the code works just fine, however in an instance like what I mentioned, it does not.</p>
  
  <p>I am using SQL Server 2008</p>