select *
from v_GS_OPERATING_SYSTEM
where caption0 like '%7%'
order by caption0

select *
from v_GS_OPERATING_SYSTEM
where caption0 like '%Windows 7%'
order by caption0

select count(distinct OS.resourceid) as ModelCount,convert(nvarchar(300),serverinfo),OS.Caption0,dt.Servicepack,
dt.extendedsupportEnddate,dt.servicepacksupportenddate,
case
when DATEDIFF(month,getdate(), dt.servicepacksupportenddate) < 0 then 'black'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 0 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 6  then 'red'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 6 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 12  then 'orange'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 12  then 'green'
ELSE ''
End As PieColor
 from [dbo].[v_GS_OPERATING_SYSTEM] OS
left join datesinfo dt on  os.caption0=dt.serverinfo where os.caption0 not like '%Server%' and  os.caption0 not like '%Windows 10%'
and (caption0 like N'%Windows 7%' or caption0 like N'%Windows 8%' or caption0 like N'%Windows Vista%')
group by dt.Servicepack,serverinfo,dt.servicepacksupportenddate,dt.extendedsupportenddate,OS.Caption0