Supervision Timing Overlap

[bgarz]

Hello all,

I have a somewhat time-sensitive request I was hoping this community might be able to help me out with. I have attached a data set that contains one month's worth of encounters between patients (signified by "ClientID") and their providers (signified by "ProviderID"). Each row herein represents one single-day, variable duration, encounter between a patient and their provider. Providers can be either an RBT or a BCBA (as labeled in "TRP BCBA or RBT"). We have fields that label the "TimeWorkedFrom" and "TimeWorkedTo" for each encounter, as well as the resultant "TimeWorkedInHours" and "TimeWorkedInMinutes". We are trying to determine the following:

For each RBT, how many (Billable, non-void) hours of care did they deliver over the course of the month? And then, (the trickier part) how many of those hours were overseen by a supervising BCBA? Supervision in this case is defined as a BCBA billing time with the same ClientID for an overlapping period of time, on the same day, during which an RBT was also billing time for that same ClientID. So, for example if an RBT was with a particular client from 2:00 - 5:00 PM on 12/5, and a BCBA was with that client on the same day from 4:00-5:00 PM, that would be 3 hours counting toward the RBT's total Billable hours for the month, and 1 hour counting toward BCBA supervision of that RBT's workload. We are trying to ultimately understand what percentage of every RBT's billable hours were supervised by BCBA's over the course of the month. This isn't immediately obvious to me how best to go about this given the format of the underlying raw data. Your guidance is much appreciated!

 

[vletm]

bgarz
I hided some of those columns (eg names no need to be there)...

Could You name rows of Your sample 'For each RBT...'?
I did two rough outputs from Your sample ... or something like that?

 

[bgarz]

Hi vletm - thank you for your reponse and the file you've shared. While these PIVOT tables are helpful in breaking out the number of hours by date, by provider type (RBT vs BCBA), by providerID, and by Client ID, this does not help me any less manually determine how many hours of BCBA supervision overlap there were for each RBT's hours during the month. I still would have to look at each patient-RBT and (in my head) count up how many hours of their treatment with each patient were also being attended by a BCBA as well. I was hoping there would be a formulaic or algoirthmic way to aggregate that and answer the original questions posed in my post. Any thoughts?

 

[vletm]

As Written:
Could You name rows of Your sample 'For each RBT...'?
...
and what kind of results of layout are You looking for?

 

[bgarz]

Apologies, vietm - I do not understand what you are asking to have added to the rows of the sample. But the desired output we are looking for is a 4-column table with a listing of: A) A list of all of the RBT's B) The sum of the billable hours performed by each of those RBT's and C) The number of BCBA-supervised hours that each RBT had and D) the Percentage Supervision for each RBT (Column C divided by Column B)

 

[vletm]

bgarz
okay ... I try again ...
If You show Your 4-column table to someone (eg for me) and
You say that there are these overlaps as You can see ...
( So far, I could figure only those four headers ... which are missing )

I would say at once:
hmm? ...
show me which rows are those overlaps,
where do those numbers come?

>> Show me some rows of that table which You're looking for ...
You can do it manually ...
and there should also be information
from which rows those values comes -
Not 'only results'!
... You're looking for 'billable hours'!

 

[vletm]

A 4-column table A) A list of all of the RBT's
... as there are total five kind of RBT's:
Left column=

Right column =
B) The sum of the billable hours performed by each of those RBT's

C) The number of BCBA-supervised hours that each RBT had ...
... there are NONE 'trickier part's
... You should explain this with clear sample with visual image from Your data.

D) the Percentage Supervision for each RBT
... could show after 'c' has solved!

 

[bgarz]

Hi vietm - There are not 5 kinds of RBT's. There are 5 kinds of employees, one of which is an "RBT". We are trying to determine for every individual one of those RBT's (distinguished from one another by their unique ProviderID) how many billable hours did each have in the month (done very easily with a quick pivot table) and also how many of their hours were supervised by BCBA's (Full-Time BCBA or Part-Time BCBA).

LP/LPA and ADMIN can be disegarded and filtered out for this exercise.

For an example, take a look at the hours for Patient ID 528253 for dates of service 12/1/18 - 12/5/18 (screenshot below). That patient had a two hour session with RBT #517111 on 12/3/18 from 17:30 - 19:30, and that patient also had a separate 2 hours session with the same RBT on 12/5/18 from 17:30 - 19:30.

The 12/3 session was overlapping with BCBA #727839 for 1 hour and 45 minutes of supervision (since the BCBA billed 17:15 - 19:15 and the RBT billed 17:30 - 19:30). The 12/5 session was unsupervised, as no BCBA was present during that session.

If this were the full data set, RBT 517111 would show 4 billable hours, and 1 hour 45 minutes of supervision. So their % supervised would be (105 minutes / 240 minutes = ) 43.75%.

This is relatively straightforward to see this manually for this single RBT and single patient, 5 day sample set, but becomes very complex to calculate manually across all patients and all providers and over long time series. I hope this example is helpful in further clarifying what we're looking for.

If anyone else out there following along has thoughts or can help in better communicating the desired analysis here, your input/suggestions/clarifications are much appreciated!

 

[vletm]

bgarz
Interesting sentences ...

There are not 5 kinds of RBT's
... terminology matters many time!
I can know only terms which You've used.
I guessed that 'Patient ID' ...maybe is 'ClientId' or how?

Your sample was different than You wanted.

I tried to follow with Your sample
until You start to write something 'complex' and so on.
If You could explain
how to calculate those hmm? 'complex' what ever those are..,
then it would be done!
If You cannot explain it, then should I or someone else start to guess?

I cleaned 'misinformation' from data-sheet.
You can see two result sheets:
> 'result_C' ... based ClientID
> 'result_P' ... based ProviderID

I left 'all' RBTs there.

 

[vletm]

Is that something like below which is 'complex'?

 

[bgarz]

vletm ...That is just a PIVOT table of total hours by employee type, by ProviderID. That is not complex at all. I would not be here asking this question if all we were trying to do was PIVOT out the underlying data. The challenging/difficult/"complex" part of this exercise is that we need to create such a table for each RBT, but BCBA supervision hours should only count as supervision hours if they occur with the same patient, on the same day, at the same time, as an RBT that is providing care and also billing that time for the same ClientID. "Complex" is not something that we are trying to calculate. Complex is an adjective, meaning complicated or challenging, and I was simply describing the part of this exercise that is harder to automate and can't be done with a simple PIVOT table, to my knowledge.

I don't know of any way to explain the project more clearly than in my original post and in the example I gave in my prior post. ProviderID and ClientID are not the same thing. ProviderID signifies the RBT or BCBA performing the service for a data row. ClientID represent the Client receiving services for each data row. My sample was not "different than what I wanted". My sample was exactly what we're trying to produce. But we need to create this in a way that formulaicly calculates the amount of overlap between BCBA hours and RBT hours for all Client's and then sums the total billable hours and supervision hours overlapping for each RBT.

 

[vletm]

bgarz
Yes, that snapshot was from Pivot-table as well as Your sample ... wanted result.
As You have written to get something like that:
the desired output we are looking for is a 4-column table with a listing of: A) A list of all of the RBT's B) The sum of the billable hours performed by each of those RBT's and C) The number of BCBA-supervised hours that each RBT had and D) the Percentage Supervision for each RBT (Column C divided by Column B)
But from file,
You could see two sheet
which are not from any Pivot-table.
Those are shown/solved daily;
based from ClientID or ProviderID.

I know term 'complex',
many times someone use that instead of term 'difficult'.
The challenge seems to be that...
If You cannot even show,
what are You looking for and
how that would be calculated
then You don't know what do You want.

If this is so clear,
then why You haven't got many other replies? Why?
I've tries to dig out details from You,
which would help You to get answer.
Have You helped Yourself? Have You?