Home Forums SQL Server 2014 Development - SQL Server 2014 CTE Query to find Unique Status for Update Per Machine RE: CTE Query to find Unique Status for Update Per Machine

  • December 28, 2017 at 8:21 am

    #1973703

    Sue_H - Wednesday, December 27, 2017 6:36 PM

    Although you didn't provide any data or ddl, I am guessing it's because you have multiple status checks for each machine and you only want the most recent one. And in that case, no you don't need to use top 1.  If that's what you need and need it by computer name, you don't even need a CTE. But the requirements aren't nearly clear enough. If you just want to query the table for the last status check for a computer and there are multiple computers and multiple status checks for each, it would just be something like MAX(UCS.LastStatusCheckTime) and group by the other columns. But again, no data or information on the other columns.
    If you want a CTE (doesn't sound like it's needed) you could just include a row_number function and get a row number for every computer ordered by last status check. Inside the CTE you would add something like ROW_NUMBER() OVER(Partition by Name0 order by LastStatusCheckTimeDesc) as RowNum
    Then when you query the CTE, it would just be where RowNum = 1 to get the most recent status check time.

    Sue

    Thank you Sue for this.
    I am not that experience with SQL, so i'm not sure how to implement what you state above. I did try to use the MAX function, but i don't think i did it properly.
    Below is the original query i was able to find the information i need to manipulate. I am using a CTE, as it was advice by one of my co-workers since a lot more can by done with it.

    SELECT
        RV.Name0,
        Case UCS.Status
            when 0 then 'State unknown'
            When 1 then 'Not required'
            When 2 then 'Not Compliant'
            When 3 then 'Compliant'
            Else 'error'
        End as 'UpdateStatus',
        UCS.LastStatusChangeTime
    FROM
    v_Update_ComplianceStatus UCS
    join v_R_System RV on UCS.ResourceID = RV.ResourceID
    join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
    join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
    join v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
    join v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
    JOIN v_Collection Col on Col.CollectionID = FCM.CollectionID and Col.Name = 'COLLECTIONNAME'
    join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = RV.ResourceID


    i have attached a screenshot of the data I get when i run the Query.
    This collection has 8 Computers, and i want the results to only show the 8, but I am getting 5137.