title: Count of SQL Server OS types for each month of last 12 months
body: <p>I have the task of creating a report that should show the different operating system types of SQL Servers in our environment for each month of the last 12 months.</p>
  
  <p>I created the following table and data is loaded inside every day:
  <img src="https://i.stack.imgur.com/jp9M6.png" alt="enter image description here">  </p>
  
  <p>I need to get OS types for each month for the last 12 months to be displayed in the following format:
  <img src="https://i.stack.imgur.com/rEaOB.png" alt="enter image description here"></p>
  
  <p>I wrote the following query but it is wrong. The struggle I have is how to get the OS types count for the last data collection of each month (not last day of each month) and display the Date in format <code>Mon YYYY</code>. I use SQL Server 2008 R2. Any ideas?</p>
  
  <pre><code>WITH cte AS (
      SELECT OSVer = CASE OSVer
                         WHEN '5.0.2195' THEN 'Windows Server 2000'
                         WHEN '5.2.3790' THEN 'Windows Server 2003'
                         WHEN '6.0.6002' THEN 'Windows Server 2008'
                         WHEN '6.1.7600' THEN 'Windows Server 2008 R2'
                         WHEN '6.1.7601' THEN 'Windows Server 2008 R2 SP1'
                         WHEN '6.2.9200' THEN 'Windows Server 2012'
                         WHEN '6.3.9600' THEN 'Windows Server 2012 R2'
                         ELSE OSVer
                    END,
             Date,
             ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, Date) ORDER BY Date DESC) as rn
      FROM dbo.SQLMachines
      WHERE OSName IS NOT NULL
  )
  SELECT OSVer, Count(OSVer) AS 'Count', Date
  FROM cte
  where rn = 1
  GROUP BY OSVer, Date 
  ORDER BY Date DESC
  </code></pre>