locked
Queue length RRS feed

  • Question

  • Hi

    I have a table with several million records with an arrival time and a departure time and I want to know the queue lenght at any moment in time. The granularity of the data is in minutes.

    The way I can see is doing a plus or minus for each arrival and departure whihc give me a table with times and +/-1 which in turn I can aggregate, find the lowest negative value and correct the aggregate with that as an offset to make sure I don't have negative queue sizes.

    So far my flaky late Friday thoughts.

    Any better alternatives for this certainly not unique problem?

    Cheers

    Friday, January 20, 2012 8:59 PM

Answers

All replies

  • I did not get the requirement.

    Can you post some sample input data and the output that you want to see


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Friday, January 20, 2012 9:57 PM
  • SELECT COUNT(*) FROM tbl WHERE @time BETWEEN arrival AND departure

    Or am I missing something?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by vinaypugalia Saturday, January 21, 2012 4:49 AM
    • Marked as answer by Kalman Toth Thursday, January 26, 2012 11:56 AM
    • Unmarked as answer by Naomi N Wednesday, March 21, 2012 2:26 AM
    Friday, January 20, 2012 11:23 PM
  • Absolutely correct, just doesn't perform on 25 million rows of data.

    Thanks for your time anyway.

    And sorry for the late reply.

    Cheers

    Sunday, March 18, 2012 1:59 AM
  • It's a one off! And yes, it still makes sense to create some indexes on department , dates and times

    Essentially I'm not a hardcore SQL guy, just using SQL to help making sense of large volumes of healthcare data.

    In this case we talk about all attendances at several Accident & Emergency departments, for which are recorded the arrival date, arrival time, start investigation time, investigation duration, start treatment time, treatment duration, and departure time.

    Given that the queue length (the number of patients at a certain station at a certain time) seems relevant for our analytics, I want to know this queue length at each minute of the year for each department. We already suspect from 'experience' that an emergency department is a bit more of a dangerous place with both very low and very high queue lenghts (when low additional staff may have to be called up , chausing delays, when high they just max out, and patients have to wait dangerously long).

    Obviously we cannot initially know the number of persons in a queue at the beginning of the year. But since in reality there cannot be negative queue lengths, we can know this with almost surety at the end of the year. It's the negative offset I find and for which I have to correct.

    BTW simple counts run reasonably fast even wh indexes, but as soon we start with BETWEENs the wheels come off.

    Thanks for all your supportive thoughts

    Tuesday, March 20, 2012 1:52 PM
  • PHEC, here's a forum thread that has a very similar issue to the one you posed: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/24ca4768-58a0-4a05-a7ec-98a93c4dd545/#c0888b31-6dd3-40bb-9fce-2b40d671706b.  The gist of the issue is that range indices or R-trees are hard to get in SQL Server 2008, even though many queries would benefit from such a setup.  There are a number of reasons simply counting records could be much faster than your current query.
    • Marked as answer by PHEC Thursday, March 29, 2012 9:47 AM
    Tuesday, March 20, 2012 2:04 PM
  • @InLocoAbsentia

    That's neat! certainly for IP addreses, any more references for this?

    Though not sure if this will work well with datetime ranges

    Wednesday, March 21, 2012 12:53 AM
  • You would next to write some function to take in a datetime and output a real number that is a subset of some range on which you define the spatial index.  Maybe you could have a scalar function to create the representation, make a computed column using the function, and create a spatial index on the column.  It's very strange stuff, for sure, but it might get you what you need.

    It will be sad when someone, somewhere makes an out-of-the-box solution for date ranges that might obviate all this work, but who knows how much longer that will be.

    Thursday, March 22, 2012 6:50 PM
  • OK, I give the answer to InLocoAbsentia, as I've learned something here.

    found some more at:
    http://explainextended.com/2009/07/01/overlapping-ranges-mysql/

    And ordered Alastair Aitchison's "Pro Spatial with SQL Server 2012", as that promises some more useful stuff in this direction.

    little example:

    DECLARE @g geometry;
    DECLARE @h geometry;
    SET @g = 'LINESTRING(0 0, 0 3)';
    SET @h = 'LINESTRING(0 2.3, 0 4.2)';
    SELECT @g.STOverlaps(@h);
    SET @h = 'POINT(0 2)'
    SELECT @g.STContains(@h);

    --gives 1 in both cases (which is Yes)

    So points are delimited by spaces and the coordinate can be a float (thus also numeric datetime)

    thank you all

    Thursday, March 29, 2012 9:47 AM