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
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