Count how many times a rows enter time is within the enter and exit times of all other rows

I am looking to estimate a queue length for historical data at the time a record enters the queue. I would like to do this by counting how many of the rows in the data set have an enter time less than the enter time of the record, and an exit time greater than the enter time of the record.

I have created a data set that separates dates and times, which I thought would make it easier to work with, but I am having trouble getting a count of rows for each record in the data set. I have tried doing a simple aggregate count, which works for a single row, but I do not know how to make a query that will do the count for every row in the data set.

For Example I have a data set that looks like this:

RecordID | Enter_Date_Time       | Exit_Date_Time
1          2020-09-01 6:00:00 AM   2020-09-02 7:00:00 AM
2          2020-09-01 6:00:00 AM   2020-09-02 8:00:00 AM
3          2020-09-03 4:00:00 AM   2020-09-03 3:00:00 PM
4          2020-09-02 4:00:00 AM   2020-09-04 6:00:00 AM
5          2020-09-02 6:00:00 AM   2020-09-02 8:00:00 AM
6          2020-09-05 6:00:00 AM   2020-09-07 7:00:00 PM
7          2020-09-07 3:00:00 AM   2020-09-07 9:00:00 AM
8          2020-09-07 6:00:00 AM   2020-09-08 8:00:00 AM
9          2020-09-08 6:00:00 AM   2020-09-08 9:00:00 PM
10         2020-09-08 4:00:00 AM   2020-09-09 6:00:00 AM

And I would like it to look like this:

RecordID | Enter_Date_Time       | Exit_Date_Time        | Queue_Length
1          2020-09-01 1:00:00 AM   2020-09-02 7:00:00 AM   1
2          2020-09-01 6:00:00 AM   2020-09-02 8:00:00 PM   2
3          2020-09-03 4:00:00 AM   2020-09-03 3:00:00 PM   2
4          2020-09-02 4:00:00 AM   2020-09-04 6:00:00 AM   2
5          2020-09-02 6:00:00 AM   2020-09-02 6:00:00 AM   3
6          2020-09-05 6:00:00 AM   2020-09-07 7:00:00 PM   1
7          2020-09-07 3:00:00 AM   2020-09-07 9:00:00 AM   2 
8          2020-09-07 6:00:00 AM   2020-09-08 8:00:00 AM   3
9          2020-09-08 6:00:00 AM   2020-09-08 9:00:00 PM   2
10         2020-09-08 4:00:00 AM   2020-09-09 6:00:00 AM   1

My current query looks like this for one single record and manually entering the times for the row:

SELECT COUNT(*) 
FROM tbl
WHERE Enter_Date_Time >= '2020-09-02 6:00:00 AM'
   AND Exit_Date_Time <= '2020-09-02 6:00:00 AM'

I need a simple operation like this to be done for every row in the data set and have the times in the where clause be the enter time for the record.

Your expertise is greatly appreciated!

Answer

One option uses a correlated subquery:

select
    t.*,
    (
        select 1 + count(*)
        from mytable t1
        where t1.enter_date_time < t.enter_date_time and t1.exit_date_time > t.enter_date_time
    ) queue_length
from mytable t

Advertisement

Answer