gavin_rudge
asked on
waiting in line query
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
FROM [Table1]
UNION SELECT ID, leave AS event,-1 AS counter
FROM [Table1]
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?
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
FROM [Table1]
UNION SELECT ID, leave AS event,-1 AS counter
FROM [Table1]
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?
ASKER
Hi Ian. I follow the logic of your solution and it looked the way to go, but when I to make the view I got syntax errors in the create view SQL. Seems to be some debate on other Acess discussion sites that Create View can actually be used through Access SQL viewer? I'm on shaky ground here as I've never built this sort of query before. Also if it can, is is OK to use a union query to make the view?
ASKER
Sorry John I called you Ian!
sorry,
looking at your syntax, i didn't realize you were actually in access, i thought you were in sql server, that explains the error. -which to create a query in access is much easier.
just create the query in sql view. copy the code you have in your question into the sql window and save it with a name you want, i chose vw_QueueEvents.
with that, then create another new query with the code, call it whatever you wish.
select First(Now()) as [Now], Sum([event]) as [In_Queue]
from vw_QueueEvents
where event <= Now();
-that'll give you your output. you wish I believe.
-John
looking at your syntax, i didn't realize you were actually in access, i thought you were in sql server, that explains the error. -which to create a query in access is much easier.
just create the query in sql view. copy the code you have in your question into the sql window and save it with a name you want, i chose vw_QueueEvents.
with that, then create another new query with the code, call it whatever you wish.
select First(Now()) as [Now], Sum([event]) as [In_Queue]
from vw_QueueEvents
where event <= Now();
-that'll give you your output. you wish I believe.
-John
ASKER
Thanks John. I think your solution has almost got me there, but just it returned one row, todays date with a (large) number as In_queue. It is doing the arithmatic right, but only in aggregate for the entire record set, not on each row.
I thought you just wanted a running total?
-you want one for each record? Try this as your second query.
select t1.[event]
, (SELECT Sum(t2.[counter]) as [In_Queue]
from vw_QueueEvents as t2
where t2.event <= t1.event
group by t2.event, t1.event) as Running_Total
from vw_QueueEvents as t1
Order by t1.event
-also-my apologies, i was summing(event) rather than sum(counter), could have added to the count being off.
this will total the counter column for each record in the past or current on each timestamp value in your original query.
-John
-you want one for each record? Try this as your second query.
select t1.[event]
, (SELECT Sum(t2.[counter]) as [In_Queue]
from vw_QueueEvents as t2
where t2.event <= t1.event
group by t2.event, t1.event) as Running_Total
from vw_QueueEvents as t1
Order by t1.event
-also-my apologies, i was summing(event) rather than sum(counter), could have added to the count being off.
this will total the counter column for each record in the past or current on each timestamp value in your original query.
-John
ASKER
Hi John,
Stll struggling I'm afraid. I get one of those "at most one record may be returned..." errors. Is this because in the Select statement, what is being slected could be more than one record as some of these events may be simultaneous?
Stll struggling I'm afraid. I get one of those "at most one record may be returned..." errors. Is this because in the Select statement, what is being slected could be more than one record as some of these events may be simultaneous?
Let's just scrap the subquery/ field 2 and take a different and more straightforward approach. most efficient would be anyone's guess.
select t1.event
, sum(t2.counter) as [In_Queue]
From vw_QueueEvents as t1
, vw_QueueEvents as t2
Where t1.event <= t2.event
group by t1.event
Order by t1.event
-John
ASKER
Hi John,
I think this will work now. The SQL as is gives an odd result in the in-queue, but I think I've found the problem and can tweak it, there are some duplicate event times so I plan to group by unique ID and order by event. The t1, t2 thing, now I follow it, should solve this and a bunch of other problems I have. Trouble is this takes forever to run as the database is quite large (I know I need to migrate to SQL server, it's on my to do list). I will leave it going overnight and confirm that it works when I get the result back.
I think this will work now. The SQL as is gives an odd result in the in-queue, but I think I've found the problem and can tweak it, there are some duplicate event times so I plan to group by unique ID and order by event. The t1, t2 thing, now I follow it, should solve this and a bunch of other problems I have. Trouble is this takes forever to run as the database is quite large (I know I need to migrate to SQL server, it's on my to do list). I will leave it going overnight and confirm that it works when I get the result back.
I would think that an index on [event] would help that out tremendously, as well as periodic compact/repairs.
FYI on the SQL Server migrate, i strongly recommend it. i have jobs that took 15 minutes in access db over an odbc link running almost instantaneous in sqlserver.
-John
-oh, i just had a thought too regarding your duplicate timestamp problem.
what if you changed the underlying query (vw_QueueEvents) to sum the Counter on each distinct event? (see code window) -I've removed order by, just slowing it down.
then you could use the same query to output your results and you wouldn't need to mess with the ID field which i believe you said wasn't chronological anyways, right?
-this would also shrink the size of the recordset of vw_QueueEvents which couldn't do anything besides help performance.
-John
what if you changed the underlying query (vw_QueueEvents) to sum the Counter on each distinct event? (see code window) -I've removed order by, just slowing it down.
then you could use the same query to output your results and you wouldn't need to mess with the ID field which i believe you said wasn't chronological anyways, right?
-this would also shrink the size of the recordset of vw_QueueEvents which couldn't do anything besides help performance.
-John
SELECT
[Event]
, Sum(Counter)
FROM (SELECT ID, arrive AS event,1 AS counter
FROM [Table1]
UNION SELECT ID, leave AS event,-1 AS counter
FROM [Table1])
Group by [Event];
ASKER
Hi John. This still gives an odd result though I can see how it is more elegant and am sure it's nearly the right answer. If it helps, I will attach a sample of the data in a table with your query as query1 and an excel file with the 'right' answer, in which each arrival and departure is shown in order and the effect on the number waiting is shown in the in_queue column. The couter counts 1 for an arrival and -1 for a departure. Its easy to knife and fork this in excel but not practical with the whole data set. Note that in the example the arrivals happen to have sequential IDs but this is not the case across the whole data set. Regarding SQL migration, I have some trepidation being a SQL newbie, but I can't wait to dump Access.
Q1.xls
queue.mdb
Q1.xls
queue.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks John,
This works well. Following your processes in solving this has also been a good tutorial for me as a SQL newbie.
Regards, Gavin.
This works well. Following your processes in solving this has also been a good tutorial for me as a SQL newbie.
Regards, Gavin.
CREATE VIEW vw_QueueEvents AS
( )
once you have your view setup, you could query it like:
select GetDate() as [Now], Sum([event]) as [In_Queue]
from vw_QueueEvents