title: Get rolling 12 months data in SQL Server
body: <p>I have a requirement to get the data for rolling next 12 months. This comes with a special condition to show the months with blank data if data does not exist in the database. For example - If data exists only till Jan 2017 then the result query should show Feb 2017 also but with blank data.</p>
  
  <p>Using below query I am able to get the data which exists. I don't want to add any loops to check which month is missing and add those months.</p>
  
  <pre><code>SELECT
          SiteCode
          ,CustomerName
          ,CalYear
          ,CalMonth
          ,CalDay
          , CONVERT(DATE,CONVERT(VARCHAR(4),CalYear)+'-'+CONVERT(VARCHAR(2),CalMonth)+'-'+CONVERT(VARCHAR(2),CalDay)) AS CalDate
          ,MachineDownTimes
          ,MaterialsDownTimes
          ,LineBalancingLost
          ,Others
  FROM 
          dbo.ProcessBackend
  WHERE CustomerName = 'ZAS' AND SiteCode = 'HU01'
          AND DATEFROMPARTS(CALYEAR, CALMONTH, CALDAY) 
          BETWEEN DATEFROMPARTS(DATEPART(YEAR, GETDATE()), DATEPART(MONTH, GETDATE()), 1) AND 
          DATEFROMPARTS(DATEPART(YEAR, (DATEADD(MONTH, 12, GETDATE()))), DATEPART(MONTH, (DATEADD(MONTH, 12, GETDATE()))), 1)
  </code></pre>
  
  <p>What would be the best option to achieve this in SQL or C#</p>