title: Retrieving last 11 months for chart
body: <p>I need the last 11 months with November and December as well as the current month for my chart but can not see the month when there is no data. And it's still called the last years that should not be.</p>
  
  <pre><code>declare @TempTable TABLE
  (
  [Order] int,
  [Month] nvarchar(3),
  [Booked] int,
  [Cancelled] int
  )
  
  declare @CurrentYear int = cast(substring(convert(varchar, getdate(), 23), 0,5) as int)
  declare @CurrentMonth int = cast(substring(convert(varchar, getdate(), 1), 0,3) as int)
  
  insert into @TempTable 
  select
  (@CurrentYear - substring(convert(varchar, A.CreatedAt, 23), 0,5)) * 11 + 
  @CurrentMonth - cast(substring(convert(varchar,A.CreatedAt, 1),0,3) as int)
  as 'Order',
  substring(convert(nvarchar, A.CreatedAt, 107), 0, 4) as 'Month',
  Count(*) as 'Booked',
  SUM(cast(A.IsCancelled as int)) AS 'Cancelled'
  FROM ReservationTbl A
  GROUP BY substring(convert(nvarchar, A.CreatedAt, 107), 0, 4),
  (@CurrentYear - substring(convert(varchar, A.CreatedAt, 23), 0,5)) * 11 +
  @CurrentMonth - cast(substring(convert(varchar,A.CreatedAt, 1),0,3) as int)
  
  
  
  select * from @TempTable WHERE [Order] &lt;&gt; (SELECT MAX([Order]) FROM @TempTable) Order By [Order] DESC
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/ezoUr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ezoUr.png" alt="enter image description here"></a></p>