How to substract between same date/time DB column in Oracle

Inod Umayanga 0
Viewed 31

Oracle SQL In the table there are status given to the each row(arrived, ExamCompleted, VitalsTaken, discharged...)where we track information about patients in the hospital. It is required to find the number of hours patient spent inside the hospital. How can I substract one column with itself where status are different.

patient_id     |      status         |  created_on
7654           |      arrived        |  2022-09-18 07:22:46
7654           |    examCompleted    |  2022-09-18 09:35:26
7654           |      vitalsTaken    |  2022-09-20 02:41:55
7654           |      discharged     |  2022-09-20 07:42:33

I need to substract arrived status date/time from discharged status date/time Table have many other columns but these are the ones I need to work with. If there are null recorded for a created_on column (for few rows) how can I ignore that particular row while showing data.

Desired result is something like

patient_id     |         timeSpent
7654           |         48:19:13

Thank you in advance

subtraction oracle database sql
2 Answers

Do a self join on the database table. The total time would be the value of column CREATED_ON when the STATUS is discharged minus the value of CREATED_ON when the STATUS is arrived for a given PATIENT_ID. The below SQL gives the result as a number of hours.

select A.PATIENT_ID
      ,(B.CREATED_ON - A.CREATED_ON) * 24 as STAY
  from PATIENTS A
  join PATIENTS B
    on A.PATIENT_ID = B.PATIENT_ID
 where A.STATUS = 'arrived'
   and B.STATUS = 'discharged'

Refer to this db<>fiddle
Also refer to: https://asktom.oracle.com/Misc/DateDiff.html

userJ4590

Correct?? That what you expected?

SELECT DISTINCT patient_id,
                MAX(created_on) OVER(PARTITION BY patient_id) - MIN(created_on) OVER(PARTITION BY patient_id)
  FROM PATIENTS
viking0
Related