Find the OID with a start and stop encompassing the connect and disconnect

Jan Nielsen 0
Viewed 19

I have a client connection table which does not link directly to the associated session table, unfortunately. So, I need to find the OID for each C.ID where the C.ID's CONNECT and DISCONNECT fall-within an S.OID START and STOP. A row in C can only map to one OID.

For a table like:

Table C

 ID      CONNECT         DISCONNECT
  1  2022-09-01T00:07  2022-09-01T00:25

and a table like:

Table S

 ID OID       START            FINISH        STATE
  1   1  2022-09-01T00:00  2022-09-01T00:02   STOP
  2   2  2022-09-01T00:05  2022-09-01T00:08  PAUSE
  3   2  2022-09-01T00:10  2022-09-01T00:18  PAUSE
  4   2  2022-09-01T00:25  2022-09-01T00:38   STOP
  5   5  2022-09-01T00:45  2022-09-01T00:58   STOP

C.ID = 1 "falls within" S.ID 2, 3, 4 which are all OID 2. To find this, first create table S' which is a list of the start and stop of each OID:

Table S'

 OID      START            STOP          STATE
  1  2022-09-01T00:00  2022-09-01T00:02   STOP
  2  2022-09-01T00:05  2022-09-01T00:38   STOP
  5  2022-09-01T00:45  2022-09-01T00:58   STOP

Then for each row in C, find the OID with

  OID.START <= C.CONNECT
AND
  OID.STOP  >= C.DISCONNECT

But how do I write this in SQL?

greatest-n-per-group sql postgresql
1 Answers

Here's a CTE-based solution which assumes all S rows have a 'CLOSED' end-row. It'd be better if this used the max(id) so the paused final state would be captured, too.

WITH
    start AS (
      SELECT * FROM S WHERE oid = id
    )
  , stop  AS (
      SELECT * FROM S WHERE state = 'CLOSED'
  )
  , duration AS (
      SELECT
          start.oid
        , stop.id
        , stop.state
        , start.start  AS start_date
        , stop.finish  AS stop_date
      FROM start
      JOIN stop
        ON start.oid = stop.oid
      ORDER BY
          start.oid
        , stop.id
  )
SELECT
    duration.oid
  , duration.id
  , c.id               AS cid
  , duration.start_date
  , c.connect
  , c.disconnect
  , duration.stop_date
FROM duration, C c
WHERE
      c.connect    >= duration.start_date
  AND c.disconnect <= duration.stop_date
ORDER BY cid
Jan Nielsen0
Related