Comparing minute of day with multiple variables

[andrewmo]

Hello

I am trying to build load statements that would compare surgical block times to actual times to determine operating room utilization.  I have tried using interval match but it doesn't seem to get me the results I expected.

I have one load statement that creates a table which contains the following elements:

- service line

- start date

- end date

- start time

- end time

- campus

- day of week

- week number

- room

In order for a case to be considered in block the case start and stop times must meet all the above criteria. For example:

Service Line Orthopedics has block on the 3rd Monday of the month at the Main OR between 0700-1500 in 2 different rooms.

Case A is an orthopedics case which occurred on the 3rd Monday of the month at the Main OR with a start time of 1200 and end time of 1600.  This particular case would be considered in-block between 1200 and 1500 and then 1500-1600 would be considered out of block. I would also need to account for the number of rooms going at the same time to account for overbooking (if orthopedics had 3 cases going at 1200, 2 would be in-block and the third would be overbook)

What would be the best method for tackling this request?

Thanks in advance

[MarcoWedel]

please post an example application including some sample data as well as your expected result.

thanks

regards

Marco

[andrewmo]

I believe that is part of my problem; not sure how to script it for my expected result. Here is pseudo code:

CASES:

LOAD      CASE_ID, PROC_DATE, PROC_DOW, PROC_WEEKNUM,  IN_DTTM, OUT_DTTM, LOCATION, ROOM,                PROV_ID, SERVLINE

FROM     OR.QVD;

BLOCK:

LOAD     SERVLINE, DOW, WEEKNUM, STARTDATE, ENDDATE, STARTTIME, ENDTIME, CAMPUS, ROOM

FROM     BLK.QVD;

Here is the criteria for in-block:

SERVLINE from CASES = SERVLINE from BLOCK

PROC_DATE from CASES must be between STARTDATE and ENDDATE from Block AND

IN_DTTM from CASES must be >= STARTTIME from BLOCK AND

OUT_DTTM from CASES must be <= ENDTIME from BLOCK AND

PROC_DOW from CASES = DOW from BLOCK AND

PROC_WEEKNUM from CASES = WEEKNUM from BLOCK AND

LOCATION from CASES = CAMPUS from BLOCK AND

(tricky part)

How do I create a Boolean flag : if minute of day = minute of day from STARTDATE = 1 ELSE 0

I want to be able to see at 07:33 SERVLINE 'Ortho' had 3 cases going and at 07:35 SERVLINE 'Ortho' had 2 cases going because one finished...

[MarcoWedel]

maybe helpful:

QlikCommunity Tip: How to get answers to your post?

Its not about the answer, rather it’s about the question…

regards

Marco