SELECT CONVERT (Varchar (10), OSYS.InstallDate0, 102) AS 'R01 WIN 7 INSTALL DATE', COUNT(DISTINCT SYS.ResourceID) AS 'R01 COUNT'
,COUNT(DISTINCT CASE WHEN SMSIS.SMS_Installed_Sites0 = 'abq') THEN SYS.ResourceID ELSE NULL END) AS 'ABQ COUNT'
FROM dbo.v_R_System SYS
LEFT OUTER JOIN dbo.v_GS_OPERATING_SYSTEM OSYS ON SYS.ResourceID = OSYS.ResourceID
LEFT OUTER JOIN dbo.v_RA_System_SMSInstalledSites SMSIS ON SYS.ResourceID = SMSIS.ResourceID
WHERE SYS.Operating_System_Name_and0 LIKE '%workstation%'
AND SYS.Client0 = '1'
AND SYS.Obsolete0 = '0'
AND OSYS.Caption0 LIKE '%7%'
GROUP BY CONVERT (Varchar (10), OSYS.InstallDate0, 102)
ORDER BY CONVERT (Varchar (10), OSYS.InstallDate0, 102)
COUNT() doesn't count NULL values, so the above only counts the distinct values whe SMS_Installed_Sites0 = 'abq'
Tom
No comments:
Post a Comment