this is kind of complicated 😉 and posting details about my problem in the Community generally helps me having good thoughts myself, plus someone might have an idea to help me:
I am trying to track the goods that have been delivered into our warehouse_center with a certain "delivery_slip" and a certain "item_no".
The items received then were transferred to just one "Track" with 600 pieces of whatever.
At that moment, that Track (generally all the Tracks out of that delivery) are associated a specific "Serial_Lot_No" which in theory should help track the progress of the goods from that delivery from the receiving_area to the shipping_area.
The first issue is, when I try to extract from the database all bookings (transactions) that have been effected on that Track and that "Serial_Lot_No", there is a number of them that ends at some point - but that is not on the shipping_area (I can tell that from the "Transaction_Code" of the booking). Also, the chain ends on a negative booking - generally, one negative and one positive booking are effected at exactly the same time, that is when the goods physically arrive at some destination - goods are never booked off some location until they arrive at another, so we cannot track them "on the road".
=> Luckily, the "Serial_Lot_No" remains the same - for one part anyway: 12 pieces of that "Track" have been transferred to another on the same "Serial_Lot_No" for whatever reason and then packed - that's generally as far as I can track a "Track".
<=> How to find the other 588 pieces?
Oh F* - sorry - it seems there are a number of other such pairs: Again and again, sets of 12 pieces were booked off that "Track" and onto another, which I can only track by looking at the timestamp - a simple thing for me, but it requires actual thinking and is probably quite hard to code ...
What I can do maybe is look for every booking on that one "Track" that was received which does not have a second booking to go with it - and parse all of those, disregarding the "Track", keeping only the "Serial_Lot_No" equal, and look whether there is a positive booking to go with it, with the same quantity and with a new "Track". That means quite some looping around ... but then I can try to track every one of those new "Tracks" as far as I can.
OK - easy enough so far: I just have to sort the primary table by Timestamp, that should give me groups of normally two - I just have to add some kind of marker to those bookings which do not have a twin - those are the bookings I need in the next step.
Well - I'm just being told that it's absolutely hopeless because of the way the people work ... but nevertheless I'll carry this just a bit further.
Well - I'm thinking about how to add some kind of marker to tell whether a booking comes in a pair of two (as it normally does) or whether it's a loner - which would mean that items have probably been taken off the primary "Track" and put onto another, which I can use to track further progress.
I guess the PREVIOUS command will not help me here because I could use that only to assign the records either a Nr. "1" or a Nr. "2" - which would not directly tell me which "1s" come with a "2" and which don't ...
=> So I'll have to parse the entire table, feed the timestamp of every record into a variable, extract all the records that correspond to a certain timestamp, determine right away whether I just extracted two or just one and based on that info, I can make another small temp-table with just a timestamp and a Nr. "1" or "2". Then I can join this back to the primary table
Then I know which are the loner_bookings. To further process these, I'll have to go one step in front of my primary table which is already filtered on just the Track(s) that were received.
