Overlapping client sessions

[Anonymous]

I am working with a overlapping report where one "provider ID" overlaps with another "provider id". Not every appointment overlaps.

Background reference is this is one provider overseeing another while they are working with a "clientid". The supervisor of the appointment always bills the "97155" procedure code and the supervisee the "97153" there are other codes in the dataset but these are the ones specifically being measured.

Ultimately I need to determine the overlapping time and compare it as a percentage of overall time worked by the supervised employee. A general return of the time supervised is suficient but ideally the calculation would be per client and total for the employee. 

This one just has me completely stumped and my research is failing me, or my brain has temporarily gone on an extended coffee break. Thanks in advance if anyone can help with this.

Id	DateOfService	TimeWorkedFrom	TimeWorkedTo	TimeWorkedInHours	ClientId	ProviderId	ProcedureCode	ProcedureCodeDescription
161104602	1/26/2022 0:00	1/26/2022 12:00	1/26/2022 14:00	2	1612243	1555430	97153	Direct Therapy-KS Medicaid
161100374	1/26/2022 0:00	1/26/2022 8:30	1/26/2022 10:00	1.5	1612243	1936329	97153	Direct Therapy-KS Medicaid
161102123	1/26/2022 0:00	1/26/2022 10:00	1/26/2022 11:00	1	1612243	1700793	97153	Direct Therapy-KS Medicaid
161102510	1/28/2022 0:00	1/28/2022 11:00	1/28/2022 11:30	0.5	1432034	1431089	97155	TELEHEALTH - BCBA Oversight: KS Medicaid
161102635	1/26/2022 0:00	1/26/2022 15:00	1/26/2022 16:00	1	1454100	1700793	97153	Direct Therapy-KS Medicaid
161109584	1/26/2022 0:00	1/26/2022 13:00	1/26/2022 14:00	1	1454100	1549473	97153	Direct Therapy-KS Medicaid
161105095	1/26/2022 0:00	1/26/2022 15:00	1/26/2022 16:30	1.5	1612243	1555430	97153	Direct Therapy-KS Medicaid

[Anonymous]

Hi @ryan_mayu,
the expected output would be a percentage of the overlap so a return similar to below. I hope this makes sense. 

I can split and total each code by provider and create certain percentages, I am struggling with defining the ovelap in the BI. Any help here would be appreciated.
Providerid (with the 97153 codes)                             supervising providerID (97155 codes)               % of overlap
     clientID                                                                                                                                             %
     ClientID                                                                                                                                             %

Providerid (with the 97153 codes)                             supervising providerID (97155 codes)               % of overlap
     clientID                                                                                                                                             %
     ClientID                                                                                                                                             %

[ryan_mayu]

since i am not familiar to your business, could you pls provide the output with data based on the sample data you provide, not only the layout. 

what's more, for the 97155, i only see one provide id 1431089 and do not have any overlap with other records.

[Anonymous]

@ryan_mayu 

Here is a link to the full dataset, I apologize that the sample did not have an overlap, which negates the whole question, I was trying to keep it simple and just made things harder. different providers provider service to clients but only our supervisors provide the oversight or the 97155 codes. I am only concerned with the cross over of 97153 & 97155 codes for this specific review.  The ID column is a unique code to each service line, client and provider ID unique identifer to each person. I am trying to find the percent of time an employee is supervised versus their total time worked with clients.

Again any help is appreciated as I am completely stumped right now.

https://1drv.ms/u/s!Aj9-fJ8Jdp0o0xyzsDnb4FONpJmH?e=tLMf65

[ryan_mayu]

@Anonymous 

What's the expected output?