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

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

CodePudding user response：

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

CodePudding user response：

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