Give this a whirl:

SELECT

S.SERVERIP,

ISNULL(C.VIDCOUNT, 0) AS VIDCOUNT

FROM

(

-- Distinct server list

SELECT DISTINCT

vs.SERVERIP

FROM dbo.VID_SERVER AS vs

) AS S

CROSS APPLY

(

-- Most recent scan for each server

SELECT TOP (1)

vs2.SCANCOMPLETE

FROM dbo.VID_SERVER AS vs2

WHERE

-- Correlate to distinct server list

vs2.SERVERIP = S.SERVERIP

ORDER BY

vs2.SCANCOMPLETE DESC

) AS SC

OUTER APPLY

(

-- Count qualifying VIDs

SELECT

COUNT_BIG(DISTINCT vs3.VID) AS VIDCOUNT

FROM dbo.VID_SERVER AS vs3

JOIN dbo.VULNS AS v ON

v.VID = vs3.VID

WHERE

-- Correlate to distinct server list

vs3.SERVERIP = S.SERVERIP

-- Correlate to latest scan date

AND vs3.SCANCOMPLETE = SC.SCANCOMPLETE

-- Qualifying exploitabilities

AND vs3.EXPLOITABILITY IN ('H', 'F', 'POC')

-- Minimum base score value from VULNS

AND v.BASESCOREVALUE >= 9

) AS C;