Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I am trying to build a display that shows the utilization of each surgical block in an operating room.
To do so I have to create three main tables:
A case details table that has the case times and locations
A master calendar table
A block calendar table that contains possible dates for each block based on the day of the week and week number of the month.
I then need to use a combination of all the above tables to be able to see the count of cases going at any minute of a given day versus the amount of rooms available for that specific block at that specific time.
I would like to see something similar to this:
The issue I have as seen in the pic above is I am not able to capture distinct minute of day (notice 8:21 and 8:28) nor am I able to figure out the logic to capture the number of cases going at that time of day.
I have attached a few dummy cases to my logic in the QVW below.
Please let me know if you have any suggestions.
Thanks again
Check the attached
Hi Andrew,
I'm pretty sure I've got a theatre utilisation dashboard I created before which I can share
I'll check my server when I have a moment (later this evening)
Try this
BLOCKMINS:
NoConcatenate
Load DISTINCT interval(Floor(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;
INNER JOIN (BLOCKMINS)
IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)
LOAD StartTime as BlkStart, EndTime as BlkEnd
Resident BLOCKCAL;
Hi Sunny
Thanks for the advice. While this did solve the duplicate minute of day issue, now it appears the intervalmatch is off somehow:
8:27 should have a 2, but displays one. My expression for the Available column is =count(BlkStart) which comes from the BLOCKMINS load.
Or may be round
BLOCKMINS:
NoConcatenate
Load DISTINCT interval(Round(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;
INNER JOIN (BLOCKMINS)
IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)
LOAD StartTime as BlkStart, EndTime as BlkEnd
Resident BLOCKCAL;
Try Round instead of Floor
Awesome! That looks like it should do the trick for the Available column, any advice for the Going column?
I thought the INMINS load statement would work but I can't figure it out:
INMINS:
NoConcatenate
LOAD DISTINCT interval(frac(MinOfDay),'h:mm') as BlkMinOfDayTEST,Dt,ServLine,Loc, count(distinct LOG_ID) as Cnt
RESIDENT CASESFIN
GROUP BY interval(frac(MinOfDay),'h:mm'),Dt,ServLine,Loc;
I was thinking my expression for Going should be =if(BlkMinOfDayTEST = BlkMinOfDay,Cnt,0) but it does not return any results
I am not sure what you are counting within Going? Would you be able to elaborate a little?
I want to be able to see the amount of LOG_IDs whose InTm and OutTm fall in the range of the BlkMinOfDay.
For example:
LOG_ID 123 has an InTm of 08:20 and an OutTm of 14:07
LOG_ID 345 has an InTm of 10:33 and an OutTm of 11:18
On the table with the BlkMinOfDay I would expect to see from 8:20-10:33 2 Available and 1 going, but then from 10:33-11:18 2 Available and 2 Going; then back to 2 Available and 1 going at 11:19..
Does that make sense?
Check the attached