Here is a statement that works we came up with in a hour. We will tweak tomorrow. For the curious minds.
WITH T as
(
SELECT
ROW_NUMBER() OVER (PARTITION By VM_Name ORDER BY Create_Time ASC ) as Num,
Create_Time,
VM_NAME
FROM
VPX_ENTITY EN
INNER JOIN VPX_VM V on EN.ID = V.ID
INNER JOIN VPX_EVENT E on E.VM_Name = EN.Name
)
SELECT DISTINCT
T.VM_NAME,
T.CREATE_TIME,
E.USERNAME,
DateDiff(D, T.Create_Time, Getdate()) as DaysSince
FROM
T
INNER JOIN VPX_EVENT E on T.VM_Name = E.VM_Name and E.Create_Time = T.Create_Time
WHERE
T.num = 1
and DateDiff(D, T.Create_Time, Getdate()) < 600
