I have a scenario where in we have Invoice Number and Date column. For each invoice we have different differnt dates.Based on the date I need to calculate the Invoice version like 1,2,3.
For instance the date is like :
INVOICE, DATE
INV1, 03-01-2013
INV1, 09-01-2013
INV1, 06-01-2013
INV1, 20-01-2013
INV1, 29-01-2013
INV2, 04-02-2013
INV2, 01-01-2013
INV3, 14-10-2103
INV3, 12-08-2103
INV3, 10-04-2103
INV3, 10-10-2013
I would like to calculate the version number on the basis of date for each invoice. I would like to see like :
=> Sort by two criteria: Inv_ID (primary) and date (secondary), both in asc. order
=> In a new field (in every record from the 2nd on, the very 1st is different), you have to
- check whether the Inv_ID in the record above (use PEEK() to find out) is the same
=> If that is not the case, you have encountered a new INv_ID, so put a 1 in a "vrs." field
=> If that is the case, you are still in the same Inv_ID_group, so add 1 to the "vrs" field in the rec. above (use PEEK() to query that value)
=> Finally, in the very 1st record, you necessarily have a brand-new Inv_ID_group, you cannot execute a query with PREVIOUS(), it will fail, so put a 1 in that record (in your "vrs" field)
=> All that has to go into one (rather large) nested IF_construct, which might look like this:
