SQLNu - Wednesday, December 27, 2017 5:18 PM

I'm making a query which can provide the last compliance status for updates for a collection per machines[SCCM Database]. The issue that I am having is that when i run the query on multiple machines, i am getting incorrect results. Currently, i am trying it with a CTE, but i don't know how to pass the computer value to the CTE function, below is the query i am using which is returning nothing when I run it, but if I move the computername inside the CTE function it works as intended.


WITH cteUpdateComplianceStatus (myResourceID, myUpdateStatus,myComp,myLastStatusCheckTime)
AS
(
    SELECT TOP 1

            UCS.ResourceID AS 'myResourceID',
            UCS.Status AS 'myUpdateStatus',
            RS.Name0 AS 'myComp',
            UCS.LastStatusCheckTime AS 'myLastStatusCheckTime'

    FROM dbo.v_Update_ComplianceStatus UCS
    JOIN dbo.v_R_System RS ON UCS.ResourceID = RS.ResourceID

    WHERE UCS.LastStatusCheckTime IS NOT NULL
    ORDER BY UCS.LastStatusCheckTime DESC
)

SELECT
    cte.myComp AS 'COMPUTER',
    cte.myUpdateStatus AS 'UpdateStatus'    
    
    FROM dbo.v_R_System vRS
    JOIN cteUpdateComplianceStatus cte ON cte.myResourceID = vRS.ResourceID
    WHERE vRS.Name0 = 'COMPUTERNAME'