SO I have this unique request from a customer and I'm a little baffled how to elegantly handle. Basically, depending on the ratio of staff to clients throughout the day (ratio changes throughout the day as clients come and go and staff come and go), there's a certain billing code applied based on the particular client-to-staff ratio in a given 15 minute billing unit. The billing is in 15 minute increments.

So, to keep an example simple:

1. Andy the client clocks in at 9:00 am. Ann the staff also clocks in at 9:00 am. At 9:15 am, one billing code is submitted for a 1:1 ratio for Andy the client as Ann was the only staff present.

2. At 9:15 am, another client, Bob the client, clocks in so the next 15 minute increment is billed at a 2:1 ratio (2 clients to 1 staff) to both Andy the client and Bob the client as Ann was the only staff present.

3. At 9:30 am, another client, Carl the client, clocks in and now so does Bess the staff so the next 15 min increment is billed as 3:2 ratio to each client, Andy, Bob, and Carl. as now there are 2 staff present (Ann and Bess)

4. One more example; Bess the staff leaves at 9:45 am so now the next 15 minute billing unit is a 3:1 billed to Andy, Bob, and Carl as Ann is the only staff present during this 15 minute billing period. This ratio changes as clients come and go and staff come and go so I need some way to track how many staff are present per 15 minutes for each client that is clocked in.

EXAMPLE BELOW (CLIENTS HAVE MALE NAMES AND STAFF HAVE FEMALE NAMES FOR EASE OF RECOGNITION):

Client List for Date: 01/28/18

Client     Time In     Time Out     Total Hours     Total Billing Units (15 min periods)  

Andy     9:00 am     11:00 am           2.00                         8

Bob       9:00 am     10:00 am           1.00                         4

Carl       9:15 am     9:30 am             0.25                         1

Dave     9:15 am     10:15 am           1.00                          4

Evan     9:30 am     10:30 am           1.00                          4

Fred      9:30 am     10:45 am           1.25                          5

Greg     9:45 am     11:00 am           1.25                           5

Hahn     9:45 am     12:00 pm           2.25                          9

Staff List for Date: 01/28/18

Staff     Time In     Time Out     Total Hours     Total Billing Units (15 min periods)

Ann        9:00 am     12:00 pm          3.00                         12

Bess      9:30 am     11:00 am          1.50                           6

Carol      9:45 am     10:15 am          0.50                           2

BELOW IS WHAT REPORT I NEED BASED ON TIMESHEET ABOVE:

So by studying the simple timetable above, here is what results need to happen based on how many staff were present during each 15 minute billing period that the client was present:

Billing Code 1 = 1 staff present during 15 min period

Billing Code 2 = 2 staff present during 15 minute period

Billing Code 3 = 3 staff present during 15 minute period

Billing Units Report by Client to Staff Ratio

Client     Tot Bill Units (15min)     Bill Code 1 Units (15min)    Bill Code 2 Units (15min)    Bill Code 3 Units (15min)

Andy                      8                                            2                                       4                                                2

Bob                        4                                            2                                       1                                                1

Carl                         1                                           1                                        0                                                0

Dave                        4                                           1                                        1                                                2

Evan                         4                                           0                                        2                                                2

Fred                         5                                            0                                        3                                                2

Greg                         5                                            0                                        3                                               2

Hahn                         9                                           4                                        3                                                2

Thanks for reading. If anyone could give me a hint on how they would do this, I would be grateful. I just need a nudge in the right direction on this one.