CODEWITHSUNDEEP
sqlpostgresql
BlackBerry
BlackBerry

Reputation: 37

Query to find simultaneous task performed by the same person

I have a table with 4 columns, ID, name, starttime, endtime. [Table1]

I need to find for each procedure the number of simultaneous procedures performed by the anaesthesian.

The output should be the Proc_id with count of simultaneous procedures. This is similar to the the expected output.

enter image description here

How do i query this?

Upvotes: 0

Views: 62

Answers (1)

Lupf
Lupf

Reputation: 168

Construct a range from start and end time using something like

tsrange (start_time, end_time, '[]')  (for exclusive range boundaries)
tsrange (start_time, end_time, '()')  (for inclusive range boundaries)

https://www.postgresql.org/docs/9.3/rangetypes.html

Use the overlaps operator '&&' to test 2 ranges

https://www.postgresql.org/docs/9.3/functions-range.html

Wrap it up in a query that may look like this

SELECT p1.proc_id,
( SELECT COUNT(*) 
FROM procedure p2 
WHERE p1.anest_name = p2.anest_name 
  AND p1.proc_id <> p2.proc_id
  AND tsrange (p1.start_time, p1.end_time, '()') && tsrange (p2.start_time, p2.end_time,'()')
) as count 
FROM procedure p1
ORDER BY p1.proc_id

What kind of range boundaries you use, depends on your understanding of "simultaneous". That may mean overlapping, contained by another timeframe or something else. You may want to fiddle around with the boundaries, but that query should match your estimated results (or at least come close to them) ...

Upvotes: 1

Related Questions