2 Replies Latest reply: Aug 7, 2013 1:50 PM by Don Lempitsky RSS

    Getting Max Date and Approver

      I have a database that looks something like this:

       

      Invoice      Vendor    Role From      Role To       Approval Date           Comments

      Number      Name

      10001      XYZ Co.  Smith                Johnson      1/2/2013                First Approval 1

      10001      XYZ Co.  Johnson            Jones          1/5/2013                Second Approval 1

      10001      XYZ Co.  Jones                Williams      1/10/2013              Final Approval1

      22222      ABC Co.  Cook                Jones           1/1/2013                First Approval 2

      22222      ABC Co.  Jones               Williams       1/4/2013                Second Approval 2

      22222      ABC Co.  Moore              Sullivan        1/5/2013                Final Approval 2

      22222      ABC Co.  Wiliams            Moore          1/4/2013                Third Approval 2

      321321    Acme In. Runner             Coyote          1/3/2013                First Approval 3

      321321    Acme In. Coyote              Wile              2/1/2013                 Final Approval3

      5555        Co. Inc.   Jones               Smith             1/24/2013              Only Approval

       

      I am trying to get the Invoice Number, Vendor Name and the earliest date with the Role From person, i.e.:

       

      Invoice   Vendor         Role From      Approval Date Comments

      Number  Name

      1001        XYZ Co.      Smith             1/2/2013           First Approval 1

      22222      ABC Co.      Cook             1/1/2013           First Approval 2

      321321    Acme Inc.     Runner          1/3/2013           First Approval 3

      5555        Co. Inc.       Jones             1/24/2013         Only Approval

       

      And also the last person to approve it:

       

      Invoice Vendor           Role To       Approval Date      Comments

      Number Name

      1001       XYZ Co.       Williams      1/10/2013           Final Approval 1

      22222     ABC Co.       Sullivan      1/5/2013             Final Approval 2

      321321   Acme Inc.     Wile            2/1/2013             Final Approval 3

      5555       Co Inc.          Smith          1/24/2013          Only Approval

       

      I tried using Group By, but it does not give the correct names or comments.  It seems to be pretty random what shows up in the comments and Roles fields.  The max and min dates seem to work fine.

      This is what I have for the Initial Approver portion.

       

      QVDLOADTEMP:

      "Invoice Number",

      "Vendor Name",

      "Role From",

      "Role To",

      "Approval Date",

      ...other fields...

      "Comments"

      from File.qvd (qvd);

       

      load *; load "Invoice Number", min([Approval Date]) as MinApproval,

      LastValue(RoleFrom) AS LastRoleFrom, LastValue(Comments) AS MinComments

      resident QVDLOADTEMP

      group by "Invoice Number";

       

      Any help is appreciated.

      Don