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:
i need os types each month last 12 months displayed in following format:
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
Post a Comment