1 Reply Latest reply on May 30, 2017 10:22 PM by adrian.curnow

    How Can I Return the Most Recent Record?

    jfmascaro Apprentice

      I am trying to build a report from the PatchHistory table in LDMS. The table contains the results of installing patches on computers. If a patch install was attempted multiple times on a single computer, then there will be an entry for each attempt in this table. I want my report to only include a single entry for each computer with a specific patch and the result of the most recent attempt.

       

      Sample Data in PatchHistory Table:

      Computer
      PatchDateResult
      Computer_APatch_15/1/2017Fail for reason x
      Computer_APatch_15/10/2017Fail for reason x
      Computer_APatch_15/20/2017Fail for reason x

       

      I only want the last row in my report. Can this be done in the Expression for Date or do I have to create a new table that contains only the most recent entries for each computer given the patch?

        • 1. Re: How Can I Return the Most Recent Record?
          adrian.curnow Employee

          Hi,

           

          To achieve the desired result an extra table will be needed to added to the Patch History view with following statement inserted in place of the table name

           

          (SELECT PH1.PATCHHISTORY_IDN AS PATCHHISTORY_IDN,

          PH1.COMPUTER_IDN as COMPUTER_IDN,

          PH1.PATCH AS PATCH,

          PH1.MESSAGE AS MESSAGE,

          PH1.CURRENTUSER AS CURRENTUSER,

          PH1.TYPE AS TYPE,

          PH1.ACTIONDATE AS ACTIONDATE,

          PH1.ACTIONCODE AS ACTIONCODE

          FROM PATCHHISTORY PH1 WHERE PH1.ActionDate =

          (SELECT  MAX( ACTIONDate) FROM PATCHHISTORY PH2

          WHERE PH1.PATCH = PH2.PATCH AND PH1.Computer_Idn = PH2.Computer_Idn

                 ))

           

          and joined to the Patch_History table on the patchhistory_idn field

           

          DataModelAddition.pngFieldAdditions.png

           

           

          Results below:

           

          Existing Model

          All.png

           

          Modified Model

           

          Top1.png