tsql - Count of SQL Server OS types for each month of last 12 months -


i have task of creating report should show different operating system types of sql servers in our environment each month of last 12 months.

i created following table , data loaded inside every day: enter image description here

i need os types each month last 12 months displayed in following format: enter image description here

i wrote following query wrong. struggle have how os types count last data collection of each month (not last day of each month) , display date in format mon yyyy. use sql server 2008 r2. ideas?

with cte (     select osver = case osver                        when '5.0.2195' 'windows server 2000'                        when '5.2.3790' 'windows server 2003'                        when '6.0.6002' 'windows server 2008'                        when '6.1.7600' 'windows server 2008 r2'                        when '6.1.7601' 'windows server 2008 r2 sp1'                        when '6.2.9200' 'windows server 2012'                        when '6.3.9600' 'windows server 2012 r2'                        else osver                   end,            date,            row_number() on (partition datediff(day, 0, date) order date desc) rn     dbo.sqlmachines     osname not null ) select osver, count(osver) 'count', date cte rn = 1 group osver, date  order date desc 

use window functions last day data each month, , use select appropriate row:

with cte (     select osver = case osver                        when '5.0.2195' 'windows server 2000'                        when '5.2.3790' 'windows server 2003'                        when '6.0.6002' 'windows server 2008'                        when '6.1.7600' 'windows server 2008 r2'                        when '6.1.7601' 'windows server 2008 r2 sp1'                        when '6.2.9200' 'windows server 2012'                        when '6.3.9600' 'windows server 2012 r2'                        else osver                   end,            date,            max(date) on (partition year(date), month(date)) maxday     dbo.sqlmachines     osname not null ) select osver, count(osver) cnt, cast(date date) cte cast(date date) = cast(maxday date) group osver, cast(date date)  order cast(date date) desc; 

the cast()s needed if date has time component.


Comments

Popular posts from this blog

java - Suppress Jboss version details from HTTP error response -

gridview - Yii2 DataPorivider $totalSum for a column -

Sass watch command compiles .scss files before full sftp upload -