How best to chart start and stop times

• kwashington

  Old Hand

  Points: 381

  More actions

  February 1, 2019 at 10:51 am

  #370395

  Our OR wants to graph out something that will show how they are operating, time-wise.  They want to know what time the first case of the day is scheduled to start, and when it actually starts.  They also want to know when the last case is scheduled to end, and when it actually ends.  I was able to pull those times into separate columns for each date easy enough, but I am struggling on how best to graph it.  No chart type I've tried actually works how I want it to.  I'm also having a hard time figuring out how to display a range of times across the left axis - generally you'd use some kind of numeric value and getting a time in there is giving me a hard time.

  Here is the an example of the data I'm returning:
  

  Here is a mock up of what I THINK I want the display to look like.  But I'm open to suggestions - this isn't very pretty but I can't think of how else to display it?
  

  I will post my query also.
  DECLARE @start_date DATE
SET @start_date = '01/01/2019'
DECLARE @end_date DATE
SET @end_date = '01/05/2019'

  select DISTINCT CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101) AS SCHED_DATE,
        REPLACE(CONVERT(VARCHAR(5),EARLIEST_SCHEDULED_TIME.EARLIEST,108), ':', '') AS SCHED_START_TIME,
        REPLACE(CONVERT(VARCHAR(5),EARLIEST_IN_ROOM_TIME.EARLIEST,108), ':', '') AS IN_ROOM_START_TIME,
        REPLACE(CONVERT(VARCHAR(5),LAST_SCHEDULED_TIME.LAST,108), ':', '') AS SCHED_END_TIME,
        REPLACE(CONVERT(VARCHAR(5),LAST_IN_ROOM_TIME.LAST,108), ':', '') AS IN_ROOM_STOP_TIME
FROM TOR100_CASE_HEADER JOIN TSM040_PERSON_HDR PATIENT
    ON TOR100_CASE_HEADER.psn_int_id = PATIENT.psn_int_id JOIN
    TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
        and TOR100_CASE_HEADER.pri_pro_int_id = TOR101_CASE_PROCEDURE.sch_pro_int_id JOIN
    TOR900_OR_DEPARTMENT ON TOR100_CASE_HEADER.or_dpt_int_id = TOR900_OR_DEPARTMENT.or_dpt_int_id LEFT OUTER JOIN
    TPM300_PAT_VISIT ON TOR100_CASE_HEADER.vst_int_id = TPM300_PAT_VISIT.vst_int_id JOIN
    TOR515_CASE_STATUS ON TOR100_CASE_HEADER.cas_sta_cod_int_id = TOR515_CASE_STATUS.cas_sta_int_id JOIN
    TRS500_PROCEDURE ON TOR101_CASE_PROCEDURE.sch_pro_int_id = TRS500_PROCEDURE.pro_int_id LEFT OUTER JOIN
    TRS601_EVENT_SCHEDULES ON TOR101_CASE_PROCEDURE.evt_sch_int_id = TRS601_EVENT_SCHEDULES.evt_sch_int_id
    INNER JOIN
        (SELECT CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101) AS START_DATE,
                MIN(TRS601_EVENT_SCHEDULES.sch_srt_ts) AS EARLIEST
            FROM TOR100_CASE_HEADER JOIN TSM040_PERSON_HDR PATIENT
                ON TOR100_CASE_HEADER.psn_int_id = PATIENT.psn_int_id JOIN
                TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
                    and TOR100_CASE_HEADER.pri_pro_int_id = TOR101_CASE_PROCEDURE.sch_pro_int_id JOIN
                TOR900_OR_DEPARTMENT ON TOR100_CASE_HEADER.or_dpt_int_id = TOR900_OR_DEPARTMENT.or_dpt_int_id LEFT OUTER JOIN
                TPM300_PAT_VISIT ON TOR100_CASE_HEADER.vst_int_id = TPM300_PAT_VISIT.vst_int_id JOIN
                TOR515_CASE_STATUS ON TOR100_CASE_HEADER.cas_sta_cod_int_id = TOR515_CASE_STATUS.cas_sta_int_id JOIN
                TRS500_PROCEDURE ON TOR101_CASE_PROCEDURE.sch_pro_int_id = TRS500_PROCEDURE.pro_int_id LEFT OUTER JOIN
                TRS601_EVENT_SCHEDULES ON TOR101_CASE_PROCEDURE.evt_sch_int_id = TRS601_EVENT_SCHEDULES.evt_sch_int_id
            WHERE DATEPART(HOUR,TRS601_EVENT_SCHEDULES.sch_srt_ts) IN ('07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18')
            GROUP BY CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101))
                AS EARLIEST_SCHEDULED_TIME ON CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101) = EARLIEST_SCHEDULED_TIME.START_DATE
    INNER JOIN
        (SELECT CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101) AS START_DATE,
                MIN(pat_in_rm_srt_dt) AS EARLIEST
            FROM TOR100_CASE_HEADER JOIN TSM040_PERSON_HDR PATIENT
                ON TOR100_CASE_HEADER.psn_int_id = PATIENT.psn_int_id JOIN
                TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
                    and TOR100_CASE_HEADER.pri_pro_int_id = TOR101_CASE_PROCEDURE.sch_pro_int_id JOIN
                TOR900_OR_DEPARTMENT ON TOR100_CASE_HEADER.or_dpt_int_id = TOR900_OR_DEPARTMENT.or_dpt_int_id LEFT OUTER JOIN
                TPM300_PAT_VISIT ON TOR100_CASE_HEADER.vst_int_id = TPM300_PAT_VISIT.vst_int_id JOIN
                TOR515_CASE_STATUS ON TOR100_CASE_HEADER.cas_sta_cod_int_id = TOR515_CASE_STATUS.cas_sta_int_id JOIN
                TRS500_PROCEDURE ON TOR101_CASE_PROCEDURE.sch_pro_int_id = TRS500_PROCEDURE.pro_int_id LEFT OUTER JOIN
                TRS601_EVENT_SCHEDULES ON TOR101_CASE_PROCEDURE.evt_sch_int_id = TRS601_EVENT_SCHEDULES.evt_sch_int_id
            WHERE DATEPART(HOUR,TRS601_EVENT_SCHEDULES.sch_srt_ts) IN ('07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18')
            GROUP BY CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101))
                AS EARLIEST_IN_ROOM_TIME ON CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101) = EARLIEST_IN_ROOM_TIME.START_DATE
    INNER JOIN
        (SELECT CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101) AS START_DATE,
                MAX(TRS601_EVENT_SCHEDULES.sch_stp_ts) AS LAST
            FROM TOR100_CASE_HEADER JOIN TSM040_PERSON_HDR PATIENT
                ON TOR100_CASE_HEADER.psn_int_id = PATIENT.psn_int_id JOIN
                TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
                    and TOR100_CASE_HEADER.pri_pro_int_id = TOR101_CASE_PROCEDURE.sch_pro_int_id JOIN
                TOR900_OR_DEPARTMENT ON TOR100_CASE_HEADER.or_dpt_int_id = TOR900_OR_DEPARTMENT.or_dpt_int_id LEFT OUTER JOIN
                TPM300_PAT_VISIT ON TOR100_CASE_HEADER.vst_int_id = TPM300_PAT_VISIT.vst_int_id JOIN
                TOR515_CASE_STATUS ON TOR100_CASE_HEADER.cas_sta_cod_int_id = TOR515_CASE_STATUS.cas_sta_int_id JOIN
                TRS500_PROCEDURE ON TOR101_CASE_PROCEDURE.sch_pro_int_id = TRS500_PROCEDURE.pro_int_id LEFT OUTER JOIN
                TRS601_EVENT_SCHEDULES ON TOR101_CASE_PROCEDURE.evt_sch_int_id = TRS601_EVENT_SCHEDULES.evt_sch_int_id
            WHERE DATEPART(HOUR,TRS601_EVENT_SCHEDULES.sch_srt_ts) IN ('07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18')
            GROUP BY CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101))
                AS LAST_SCHEDULED_TIME ON CONVERT(VARCHAR(10),TRS601_EVENT_SCHEDULES.sch_srt_ts,101) = LAST_SCHEDULED_TIME.START_DATE
    INNER JOIN
        (SELECT CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101) AS START_DATE,
                MAX(pat_in_rm_stp_dt) AS LAST
            FROM TOR100_CASE_HEADER JOIN TSM040_PERSON_HDR PATIENT
                ON TOR100_CASE_HEADER.psn_int_id = PATIENT.psn_int_id JOIN
                TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
                    and TOR100_CASE_HEADER.pri_pro_int_id = TOR101_CASE_PROCEDURE.sch_pro_int_id JOIN
                TOR900_OR_DEPARTMENT ON TOR100_CASE_HEADER.or_dpt_int_id = TOR900_OR_DEPARTMENT.or_dpt_int_id LEFT OUTER JOIN
                TPM300_PAT_VISIT ON TOR100_CASE_HEADER.vst_int_id = TPM300_PAT_VISIT.vst_int_id JOIN
                TOR515_CASE_STATUS ON TOR100_CASE_HEADER.cas_sta_cod_int_id = TOR515_CASE_STATUS.cas_sta_int_id JOIN
                TRS500_PROCEDURE ON TOR101_CASE_PROCEDURE.sch_pro_int_id = TRS500_PROCEDURE.pro_int_id LEFT OUTER JOIN
                TRS601_EVENT_SCHEDULES ON TOR101_CASE_PROCEDURE.evt_sch_int_id = TRS601_EVENT_SCHEDULES.evt_sch_int_id
            WHERE DATEPART(HOUR,TRS601_EVENT_SCHEDULES.sch_srt_ts) IN ('07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18')
            GROUP BY CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101))
                AS LAST_IN_ROOM_TIME ON CONVERT(VARCHAR(10),pat_in_rm_srt_dt,101) = LAST_IN_ROOM_TIME.START_DATE
WHERE (convert(datetime,convert(varchar(10),pat_in_rm_srt_dt,101) +' 00:00:00'))>=convert(datetime,convert(varchar(10),@start_date,101) +' 00:00:00') AND
   (convert(datetime,convert(varchar(10),pat_in_rm_srt_dt,101) +' 23:59:59'))<=convert(datetime,convert(varchar(10),@end_date,101) +' 23:59:59')
            AND cas_sta_ds NOT IN ('Cancelled')