calculating Turn Around time 1

[ibib3]

Hi

I have been asked to provide the time between cases (Turn Around)in a report and I'm stuck, I have no idea where to begin with the SQL query.
We have a single table in an Oracle 8 database that holds patient records including the start and end of each operation and session identifiers (Theatre, Date & Session ID).
What I need to do is show how much time is being wasted between cases. The data is held in the format

Theatre Date SessionId PatientId Start Finish
Th1 1/3/2009 TH1AM P332233 8:30 9:00
Th1 1/3/2009 TH1AM P123223 9:15 10:00
Th1 1/3/2009 TH1AM P339888 10:30 11:30
Th1 1/3/2009 TH1AM P339999 11:35 11:50
Th2 1/3/2009 TH2AM P776666 8:40 10:00
Th2 1/3/2009 TH2AM P887772 11:30 12:00

what I want to do is produce a report in the following format

Theatre Date SessionId
Th1 1/3/2009 TH1AM
PatientId Start Finish Turn Around
P332233 8:30 9:00
P123223 9:15 10:00 0:15
P339888 10:30 11:30 0:30
P339999 11:35 11:50 0:05

Theatre Date SessionId
Th2 1/3/2009 TH2AM
PatientId Start Finish Turn Around
P776666 8:40 10:00
P887772 11:30 12:00 1:30

Any help gratefully appreciated

Thanks

 

[Thargy]

ibib,

are the theatre date, start time and end time fields dates or something else?

The reason I ask is that dates are stored with both date and time parts (unless you take the trouble to remove the time part). If they are times stored as numbers, or two digits, a colon and two digits, then standard date handling methods won't work.

Regards

T

 

[ibib3]

Thanks for the reply, the Theatre is a text field, the Date and time fields are standard Oracle datetime fields.

The Date field holds the datepart of the operation date with the timepart always 00:00:00 eg 01/03/2009 00:00:00.

The Start and Finish fields hold the timepart of the start and finish times of the op, with the datepart always set to 01/01/1900 eg. 01/01/1900 08:30:00

Together the Theatre, Date and SessionId form a unique
theatre session, there can be multiple patients in each session with the start & finish of times of the op of each.

 

[Thargy]

ibid,

I have the following for you, which at first will not seem helpful, but bear with me.

You have columns called DATE, START and FINISH. These are all reserved and/or keywords in oracle, and therefore should not be used. Calling a date column DATE is similar to creating a function called function - a major disaster waiting to happen.

Your data is obviously corrupt, in that the first of January 1900 is used, instead of a date field, which automatically contains a time too. However, it may not be within your remit to get this altered, but you really should bring such a fundamental error to the attention of your DBA's ASAP.

So that we can sing from the same hymn sheet, I have created the following statements. Please run them, and confirm that I have gotten the data correct. Note that I have renamed fields to avoid the use of reserved words.

CREATE TABLE IBID
(
 THEATRE VARCHAR2(10),
 OPERATION_DATE DATE,
 SESSIONID VARCHAR2(10),
 PATIENTID VARCHAR2(10),
 START_DATE DATE,
 FINISH_DATE DATE
 );

which I have populated with

INSERT INTO IBID (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th1',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH1AM','P332233',TO_DATE('01 JAN 1900 08:30','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 09:00','DD MON YYYY HH24:MI'));

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th1',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH1AM','P123223',TO_DATE('01 JAN 1900 09:15','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 10:00','DD MON YYYY HH24:MI'));

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th1',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH1AM','P339888',TO_DATE('01 JAN 1900 10:30','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 11:30','DD MON YYYY HH24:MI'));

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th1',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH1AM','P339999',TO_DATE('01 JAN 1900 11:35','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 11:50','DD MON YYYY HH24:MI'));

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th2',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH2AM','P776666',TO_DATE('01 JAN 1900 08:40','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 10:00','DD MON YYYY HH24:MI'));

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th2',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH2AM','P887772',TO_DATE('01 JAN 1900 08:40','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 10:00','DD MON YYYY HH24:MI'));

Since this table now contains a meaningless combination of dates and bastardised times, I have attempted to tidy up by creating a view, which converts the data into valid dates.

CREATE OR REPLACE VIEW V_IBID
AS
SELECT THEATRE,OPERATION_DATE,SESSIONID, PATIENTID,
TO_DATE(TO_CHAR(OPERATION_DATE,'DD MON YYYY')||' '||TO_CHAR(START_DATE,'HH24:MI'), 'DD MON YYYY HH24:MI') START_DATE,
TO_DATE(TO_CHAR(OPERATION_DATE,'DD MON YYYY')||' '||TO_CHAR(FINISH_DATE,'HH24:MI'),'DD MON YYYY HH24:MI') FINISH_DATE
FROM IBID;

when I run

SELECT * FROM V_IBID;

I get the following result

THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE
Th1,01/03/2009,TH1AM,P332233,01/03/2009 08:30:00,01/03/2009 09:00:00
Th1,01/03/2009,TH1AM,P123223,01/03/2009 09:15:00,01/03/2009 10:00:00
Th1,01/03/2009,TH1AM,P339888,01/03/2009 10:30:00,01/03/2009 11:30:00
Th1,01/03/2009,TH1AM,P339999,01/03/2009 11:35:00,01/03/2009 11:50:00
Th2,01/03/2009,TH2AM,P776666,01/03/2009 08:40:00,01/03/2009 10:00:00
Th2,01/03/2009,TH2AM,P887772,01/03/2009 08:40:00,01/03/2009 10:00:00

Since these are now valid dates I can proceed to tackle your original question. Ok so far?

Regards

T

 

[Thargy]

ibid,

I realised that I added a duplicate entry in my insert statements. I've deleted it from my table IBID and added the correct entry using

INSERT INTO IBID  (THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE) 
VALUES('Th2',TO_DATE('01 MAR 2009','DD MON YYYY'),'TH2AM','P887772',TO_DATE('01 JAN 1900 11:30','DD MON YYYY HH24:MI'), TO_DATE('01 JAN 1900 12:00','DD MON YYYY HH24:MI'));

which now adds the records you mentioned.
Please bear in mind that I am using oracle 11, but analytics were available in oracle 8. Therefore, if you run the following

SELECT THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE,
(START_DATE - LAG(FINISH_DATE,1) OVER (PARTITION BY THEATRE ORDER BY THEATRE,OPERATION_DATE,SESSIONID,START_DATE))*24*60 TURN_AROUND
  FROM V_IBID
ORDER BY THEATRE;

then you should get the same output as me, i.e.

THEATRE,OPERATION_DATE,SESSIONID,PATIENTID,START_DATE,FINISH_DATE,TURN_AROUND
Th1,01/03/2009,TH1AM,P332233,01/03/2009 08:30:00,01/03/2009 09:00:00,
Th1,01/03/2009,TH1AM,P123223,01/03/2009 09:15:00,01/03/2009 10:00:00,15
Th1,01/03/2009,TH1AM,P339888,01/03/2009 10:30:00,01/03/2009 11:30:00,30
Th1,01/03/2009,TH1AM,P339999,01/03/2009 11:35:00,01/03/2009 11:50:00,5
Th2,01/03/2009,TH2AM,P776666,01/03/2009 08:40:00,01/03/2009 10:00:00,
Th2,01/03/2009,TH2AM,P887772,01/03/2009 11:30:00,01/03/2009 12:00:00,90

Note that I have left the differences as minutes, as I suspect that you will want to do some sums on these, for presenting to management.
If your version of Oracle feigns ignorance of such things as analytics, then perhaps you ought to mention to your superiors that version 8 is now out of support, and therefore as a matter of urgency they should upgrade ASAP


Regards

T

 

[ibib3]

Hi T
Many thanks for your help, this exactly what I needed.

regards
Ian