Thank you for the replies. I am trying Paul's suggestion first which utilizes 'UNION ALL'

The way the query is written below I get inflated results (counts) if a ServerIP host was scanned more than once during the period

I need to do the following next with the script:

- ensure that duplicates are removed (I was using a CTE for this previously)

- ensure that I am getting the last scan data for each IP address. (This was also part of my original CTE)

- to add [SCAN COMPLETE] columns to the final output:

After reading up on 'UNION ALL' I have a general understanding of what it does. It will return the distinct results from the table. But how can I ensure I am only counting the results from the MAX(SCANCOMPLETE) time for each host?

----

SELECT U.ServerIP, BASESCORE COUNT_BIG(U.Union1001)

FROM

(SELECT e.ServerIP, NULL AS Union1001

FROM @Example AS e

LEFT JOIN VULNS D ON d.vid = e.vid

WHERE d.BASESCORE >= 9

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

UNION ALL

SELECT e.ServerIP, 0 AS Union1001, BASESCOREFROM @Example AS e

LEFT JOIN VULNS D ON d.vid = e.vid

WHERE d.BASESCORE >= 9

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

) AS UGROUP BY U.ServerIP, BASESCORE