0

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

Any help will be appreciated.

Thanks.

user5798852
  • 11
  • 3
  • Stack Overflow is not a free code writing service, please show your code/effort and what the actual problem is. – Blue Aug 07 '16 at 01:58
  • What I'm having difficulty is in coming up with the average for active providers per month January 4.5, February 5 and so on. I tried using the example in http://stackoverflow.com/questions/14090016/count-the-number-of-rows-in-30-day-bins to apply here but was unsuccessful. – user5798852 Aug 07 '16 at 02:05
  • I don't see any code, or your attempt where you failed, to help you figure out what went wrong. – Blue Aug 07 '16 at 02:06
  • I've thought a lot about it, but don't know where to begin for that part of it. - I've just managed to work out the logic. If you can point me in the right direction I will try again. – user5798852 Aug 07 '16 at 02:33
  • For the first part, I have calculated the number of services by month using SELECT MONTH(Serv_Date), COUNT(Serv_ID) as ASU FROM Serv GROUP BY MONTH(Serv_Date); 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. – user5798852 Aug 07 '16 at 13:17

0 Answers0