How do I create monthly bins based on date ranges and join data in another table

See more:

SQL

PHP

MySQL

I am trying to figure out how to accomplish this in MySQL

I have two tables:

1) Service Table:

Serv_ID Prov_ID Serv_Date

11812 CT18T005 2016-01-21
11819 CT18T005 2016-02-23
11823 CT18T006 2016-01-25
11832 CT18T010 2016-01-26
11838 CT18T010 2016-02-23
11842 CT18T007 2016-01-25
11848 CT18T007 2016-02-25
12886 CT18T006 2016-02-26
12906 CT18T005 2016-04-21
12907 CT18T005 2016-04-28
12916 CT18T010 2016-03-22
12918 CT18T010 2016-03-29
12934 CT18T007 2016-04-29
13475 CT18T010 2016-06-07

2) Provider Table:

Prov_ID Prov_Start Prov_End

CT18T001 2016-01-01 2016-06-30
CT18T005 2016-01-01 2016-06-30
CT18T006 2016-01-01 2016-05-13
CT18T007 2016-01-01 2016-06-30
CT18T010 2016-01-15 2016-06-30


I need to compute the Average Service Units (ASU) provided per provider per month during the reporting period (01/01/2016 - 06/30/2016), taking into account the number of days the provider(s) were active during the month. So, there were 4 providers active for the entire month of February. Provider 006 was active between 01/01/2016 and 05/13/2016 and thus should be counted for the entire month of Jan - April and 13 days in May.

Month ASU/Provider

January 4/(4+15/30) assuming 30 days in a month
February 4/5
...
...
June

The final output will be a number (Average of the ASU/Provider per month) : Sum(ASU/Provider) / 6

I'm trying to generate a table for active providers by month from the Provider table and that is the part where I don't know how to proceed.

Any help will be appreciated. This is my first time here. If I've posted in the wrong section, please let me know.

Thanks.

What I have tried:

For the first part, I have calculated the number of services by month using

SELECT MONTH(ServDate), COUNT(Serv_ID) as ASU
FROM Serv
GROUP BY MONTH(ServDate);

I'm trying to generate a table for active providers by month from the Provider table and that is the part where I'm stuck.