2 Replies Latest reply: Sep 7, 2017 3:17 PM by Kelly McGrady RSS

    using MAX date

    Robert Winkel

      ok this is what I am trying to do.

       

      in the pic below it is displaying an audit log that and the response from that transaction.  I was hoping to only pull in the latest response per PatID.

      How can I do it to only display the most recent submitted date?

       

      Ideally it possible in the load?

      I will also have to add other tables at a later point if this is a factor?

       

       

       

      LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

      LOAD SubmitGUID,
          Submitted,
          RequestType,
          FacID,
          PatID,
          Request,
          Response,
          ts;
      SQL SELECT SubmitGUID,
          Submitted,
          RequestType,
          FacID,
          PatID,
          Request,
          Response,
          ts
      FROM FWDB.PAT.PatientE1TransactionHistory

      where RequestType='MED-D';

        • Re: using MAX date
          Sunny Talwar

          May be this

           

          LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

          LOAD FirstSortedValue(SubmitGUID, - Submitted) as SubmitGUID,
              Max(Submitted) as Submitted,

              FirstSortedValue(RequestType, - Submitted) as SubmitGUID,

              FirstSortedValue(FacID, - Submitted) as SubmitGUID,

              PatID,

              FirstSortedValue(Request, - Submitted) as SubmitGUID,

              FirstSortedValue(Response, - Submitted) as SubmitGUID,

              FirstSortedValue(ts, - Submitted) as SubmitGUID

          Group By PatID;

          SQL SELECT SubmitGUID,
              Submitted,
              RequestType,
              FacID,
              PatID,
              Request,
              Response,
              ts
          FROM FWDB.PAT.PatientE1TransactionHistory

          where RequestType='MED-D';

          • Re: using MAX date
            Kelly McGrady

            So you can do Max(SubmittedDate)

            and Group by everything that is not the submitted date after the from.

            Or you can do

            LastSubmitted:

            Load

            Submitted date

            From  FWDB.PAT.PatientE1TransactionHistory

            where RequestType='MED-D';

             

            TableName

            First 1

            Load

            Load

            Submitted date

            From LastSubmitted

            Order By Submitted desc

            Left Join

                Submitted,
                RequestType,

                FacID,

                PatID,

                Request,

                Response,

                ts

            FROM FWDB.PAT.PatientE1TransactionHistory

            where RequestType='MED-D';