Hey guys! Have a scenario where I need to pull data from a table with multiple intersecting criteria.
My table below is basically a warehouse schedule for trucks, where the DVRSCD column gives the action for whatever happened at the date/time.
DCI = Driver Check In, CMP = Completed. So for the first line, it would mean the driver checked in to door 4 at 7:36 this morning.
That said, I am trying to create a list of ONLY active doors, which means any completed appointments would be excluded.
Take door 4 for example. It's had several drivers check in and out, so I would need to filter out all previous lines with Door 4, and exclude lines with CMP only for the most recent entry.
In short, I need a code to
(1) Finds all DSAPPT (appointment numbers) with a DOOR value that is not blank (this means the driver is checked into a dock door).
(2) Compares the DSAPPT number to the entire column and deletes the whole line for all matching DSAPPT numbers if the order is complete. (IE, Appt 598723 shows the driver checked in at 7:36AM, then the appointment was completed at 9:14AM (where another line with the matching appointment number shows CMP. Thus, both lines (DCI, and CMP) can be deleted.
This will leave me with exactly what I'm looking for - a list of drivers who have checked in, but haven't left.
Any help would be greatly appreciated!
Thank you!
My table below is basically a warehouse schedule for trucks, where the DVRSCD column gives the action for whatever happened at the date/time.
DCI = Driver Check In, CMP = Completed. So for the first line, it would mean the driver checked in to door 4 at 7:36 this morning.
That said, I am trying to create a list of ONLY active doors, which means any completed appointments would be excluded.
Take door 4 for example. It's had several drivers check in and out, so I would need to filter out all previous lines with Door 4, and exclude lines with CMP only for the most recent entry.
In short, I need a code to
(1) Finds all DSAPPT (appointment numbers) with a DOOR value that is not blank (this means the driver is checked into a dock door).
(2) Compares the DSAPPT number to the entire column and deletes the whole line for all matching DSAPPT numbers if the order is complete. (IE, Appt 598723 shows the driver checked in at 7:36AM, then the appointment was completed at 9:14AM (where another line with the matching appointment number shows CMP. Thus, both lines (DCI, and CMP) can be deleted.
This will leave me with exactly what I'm looking for - a list of drivers who have checked in, but haven't left.
Any help would be greatly appreciated!
Thank you!
