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
