I am looking at a queuing (waiting in line) problem. I have some times that people leave [leave] and the time that they arrive [arrive] as date/time fields in table1
I want to be able to determine at the point of each arrival, how many people were in the line ahead of the person who just arrived. I have half solved the problem but have got stuck. I made a log of arrivals and departures using a union query which combines arrivals and departures in one column called event and order by event, so all of the arrivals and departures appear sequentially. I have done this using the following which works fine. At the same time I want arrivals flagged with a 1 and departures flagged as -1 so I can count people in and out of the line.
SELECT ID, arrive AS event,1 AS counter
UNION SELECT ID, leave AS event,-1 AS counter
Order by event;
This gives me every arrival and every departure in order with a 1 flag for arrivals and a-1 for departures. Then I planned to add a variable which is some sort of sequential counter to each of the records in this query, and which will change by either 1 or -1 (i.e. the value of counter) depending whether the event is an arrival or a departure and will give me a running total of how many people are waiting at the point of each event. I cannot use the underlying unique ID from table1 as a sequential counter as records were not added to the original table in the order in which they arrived. Any ideas much appreciated?