1 Reply Latest reply on Dec 10, 2017 6:07 AM by Jim Dehner

    How to show a value from latest date

    Julie Goodman

      I'll try to keep this short, however, I'm fairly new, so please be patient.

       

      We need to display a ‘status’ value from a database document, determined by the latest of 2 dates.

       

      Date field 1 shows when any part of the document was last modified; date field 2 shows the date the status was entered.

       

      Our DB developers have unfortunately broken the ODBC views for the document into 2 sections, with date fields separated.

      View one shows most of the document, with the document date. View 2 shows the 'Status question' with the status date.

      We then need to bring in a third view to show the last date the document was modified.

      Using 1 or 3 of these, using the MAX expression, the table shows the latest date in most cases, but first status in most cases

      instead of the latest status.

       

      On the attached sample data, some have displayed correctly, the second group are some which don't.

      Correct data

      ID            Date                      Status

      1077      4/12/2017            Access request form submitted to NDIS - eligibility tested - outcome pending

      1171       28/11/2017         Access request form submitted to NDIS - eligibility tested - outcome pending

      1256       14/09/2017         Eligibility not tested - declined to apply

      1314      27/10/2017         Eligibility tested - client found not eligible - permanent disability not established

       

      For many the ‘Status’ and/or date are incorrect. Some examples

      1648                      27/10/2017         Eligibility not tested - declined to apply

      Should be            27/10/2017         Access Request Form not yet submitted – gathering evidence

      2010                       30/10/2017         Access Request Form not yet submitted – gathering evidence

      Should be            26/10/2017         Eligibility not tested - declined to apply

      2164                       25/10/2017         Eligibility not tested - declined to apply

      Should be            25/10/2017         Access Request Form not yet submitted – gathering evidence

       

      Is there a way to format the date for the ‘Status’ field to be only from the latest ‘Status' date?

        • 1. Re: How to show a value from latest date
          Jim Dehner

          Hi Julie - not sure I am tracking with you because I can't find the case numbers in your expectations

          but see the attached

          I used 2 LOD statements - first to find the max date

           

           

          Then to fix the status at that date per case

           

           

          It returns this the 3rd column is the fixed status on the max date

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.