'How to create a sql statement or anonymous plsql block to increase count when only having a start time and an end time

I have an ask for a count of number of guests in a venue broken down to the minute. The data set I have available to me is the venue, the date/time the guest entered the venue, and the date/time the guest exited the venue. The business is asking for a breakdown by minute of the count of guests in the venue.

For example, guest A enters the venue at 12:00 and exits at 13:00. Guest B enters the venue at 12:30 and exits at 13:30. The expected output would show a count of 1 from 12:00 to 12:29, a count of two from 12:30 to 13:00, and back to a count of one from 13:00 to 13:30.

I’m struggling with the ask due to restrictions placed upon me. I am not authorized to make any structure changes; therefore, no DDL, which means I am restricted to SQL or anonymous PLSQL blocks. More information: however, I am unsure if it is necessary. The database version is 12.2c and it is running on AIX.

I do have a workaround where I extract the dataset as a csv and import it into a C# console application, which I wrote, but I would prefer if the ask can be conducted within the Oracle ecosystem. I appreciate any help or insight you can share about my problem.

sqlplsqloracle12caix


Solution 1:[1]

You can solve this problem with a combination of several tricks: connect by level <= 91 to create the 91 minutes for the time frame, a left join to include all minutes even if there isn't an event at that minute, a case and sum to count and sum arrivals and departures, and finally an analytic function to generate the running total of guests by adding arrivals and subtracting departures.

--The number of guests present per minute.
select
    the_minute,
    sum(arrive_counter + depart_counter) over (order by the_minute) guest_count
from
(
    --Join time and visits and count arrivals and departures.
    select
        the_minute,
        sum(case when the_minute = arrive_date then 1 else 0 end) arrive_counter,
        sum(case when the_minute = depart_date then -1 else 0 end) depart_counter
    from
    (
        --Every minute for a time period. (Change to 1441 for an entire day.)
        select timestamp '2022-01-24 12:00:00' + (level - 1) * interval '1' minute the_minute
        from dual
        connect by level <= 91
    ) minutes
    left join visit
        on minutes.the_minute = arrive_date
        or minutes.the_minute = depart_date
    group by the_minute
    order by the_minute
)
order by the_minute;

Results:

THE_MINUTE                         GUEST_COUNT
24-JAN-22 12.00.00.000000000 PM    1
24-JAN-22 12.01.00.000000000 PM    1
...
24-JAN-22 12.28.00.000000000 PM    1
24-JAN-22 12.29.00.000000000 PM    1
24-JAN-22 12.30.00.000000000 PM    2
24-JAN-22 12.31.00.000000000 PM    2
...
24-JAN-22 12.58.00.000000000 PM    2
24-JAN-22 12.59.00.000000000 PM    2
24-JAN-22 01.00.00.000000000 PM    1
24-JAN-22 01.01.00.000000000 PM    1
...
24-JAN-22 01.28.00.000000000 PM    1
24-JAN-22 01.29.00.000000000 PM    1
24-JAN-22 01.30.00.000000000 PM    0

Solution 2:[2]

You can use:

SELECT timestamp AS time_from,
       LEAD(timestamp) OVER(ORDER BY timestamp) AS time_to,
       SUM(SUM(change_in_guests)) OVER (ORDER BY timestamp) AS guests
FROM   guests
UNPIVOT(
  timestamp FOR change_in_guests IN (
    entry AS +1,
    exit  AS -1
  )
)
GROUP BY timestamp;

Which, for the sample data:

CREATE TABLE guests (id, entry, exit) AS
SELECT 'A', DATE '2022-01-25' + INTERVAL '12:00' HOUR TO MINUTE, DATE '2022-01-25' + INTERVAL '13:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'B', DATE '2022-01-25' + INTERVAL '12:30' HOUR TO MINUTE, DATE '2022-01-25' + INTERVAL '13:30' HOUR TO MINUTE FROM DUAL;

Outputs:

TIME_FROM TIME_TO GUESTS
2022-01-25 12:00:00 2022-01-25 12:30:00 1
2022-01-25 12:30:00 2022-01-25 13:00:00 2
2022-01-25 13:00:00 2022-01-25 13:30:00 1
2022-01-25 13:30:00 null 0

If you want it minute-by-minute then:

WITH minutes (minute, time_to, guests) AS (
  SELECT timestamp,
         LEAD(timestamp) OVER(ORDER BY timestamp),
         SUM(SUM(change_in_guests)) OVER (ORDER BY timestamp)
  FROM   guests
  UNPIVOT(
    timestamp FOR change_in_guests IN (
      entry AS +1,
      exit  AS -1
    )
  )
  GROUP BY timestamp
UNION ALL
  SELECT minute + INTERVAL '1' MINUTE,
         time_to,
         guests
  FROM   minutes
  WHERE  minute + INTERVAL '1' MINUTE < time_to
)
SEARCH DEPTH FIRST BY minute SET order_rn
SELECT minute,
       guests
FROM   minutes;

Which outputs:

MINUTE GUESTS
2022-01-25 12:00:00 1
2022-01-25 12:01:00 1
2022-01-25 12:02:00 1
... ...
2022-01-25 12:28:00 1
2022-01-25 12:29:00 1
2022-01-25 12:30:00 2
2022-01-25 12:31:00 2
... ...
2022-01-25 12:58:00 2
2022-01-25 12:59:00 2
2022-01-25 13:00:00 1
2022-01-25 13:01:00 1
... ...
2022-01-25 13:28:00 1
2022-01-25 13:29:00 1
2022-01-25 13:30:00 0

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jon Heller
Solution 2

Related Questions